Вопрос 32: Доступ к нескольким таблицам. Обзор хранимых процедур. Вызов хранимых процедур.

Доступ к нескольким таблицам одновременно

SELECT Таблица1.поле1, Таблица2.поле1, Таблица2.поле2 [, ...]
FROM Таблица1, Таблица2
WHERE Таблица1.поле1 = Таблица2.поле1

Про хранимые процедуры

Примеры из лабы 4:

USE AdventureWorks;

GO
CREATE PROC Sales.GetDiscount
AS
    SELECT Description,
           DiscountPct,
           Type,
           Category,
           StartDate,
           EndDate,
           MinQty,
           MaxQty
    FROM Sales.SpecialOffer
    ORDER BY StartDate, EndDate;

GO
EXEC Sales.GetDiscount;

USE AdventureWorks;

GO
CREATE PROC Sales.GetDiscountForCategory @Category nvarchar(50)
AS
    SELECT Description,
           DiscountPct,
           Type,
           Category,
           StartDate,
           EndDate,
           MinQty,
           MaxQty
    FROM Sales.SpecialOffer
    WHERE Category = @Category
    ORDER BY StartDate, EndDate;
GO
EXEC Sales.GetDiscountForCategory 'Reseller';

USE AdventureWorks;

GO
CREATE PROC Sales.GetDiscountForCategoryAndDate @Category nvarchar(50), @DateToCheck datetime = NULL
AS
BEGIN
    IF @DateToCheck IS NULL
        SET @DateToCheck = GETDATE();

    SELECT Description,
           DiscountPct,
           Type,
           Category,
           StartDate,
           EndDate,
           MinQty,
           MaxQty
    FROM Sales.SpecialOffer
    WHERE Category = @Category AND EndDate <= @DateToCheck
    ORDER BY StartDate, EndDate;
END
GO
EXEC Sales.GetDiscountForCategoryAndDate 'Reseller';
GO
BEGIN
    DECLARE @DateToCheck datetime;
    SET @DateToCheck = DATEADD(year, -16, GETDATE());
    PRINT @DateToCheck;
    EXEC Sales.GetDiscountForCategoryAndDate 'Reseller', @DateToCheck;
END

USE AdventureWorks;

GO
CREATE PROC Sales.AddDiscount @Description nvarchar(50),
                              @DiscountPtc smallmoney,
                              @Type nvarchar(50),
                              @Category nvarchar(50),
                              @StartDate datetime,
                              @EndDate datetime,
                              @MinQty int,
                              @MaxQty int,
                              @NewProductID int = 1 OUTPUT
AS
BEGIN
    BEGIN TRY
        INSERT INTO Sales.SpecialOffer (Description,
                                        DiscountPct,
                                        Type,
                                        Category,
                                        StartDate,
                                        EndDate,
                                        MinQty,
                                        MaxQty)
        VALUES (@Description,
                @DiscountPtc,
                @Type,
                @Category,
                @StartDate,
                @EndDate,
                @MinQty,
                @MaxQty);
        SET @NewProductID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.ErrorLog (ErrorTime,
                                  UserName,
                                  ErrorNumber,
                                  ErrorSeverity,
                                  ErrorState,
                                  ErrorProcedure,
                                  ErrorLine,
                                  ErrorMessage)
        VALUES (GETDATE(),
                SUSER_SNAME(),
                ERROR_NUMBER(),
                ERROR_SEVERITY(),
                ERROR_STATE(),
                ERROR_PROCEDURE(),
                ERROR_LINE(),
                ERROR_MESSAGE());
    END CATCH

    RETURN
END

1. https://github.com/EmptyShadow/eltech_dds/blob/master/lab4/task21.sql.
2. https://github.com/EmptyShadow/eltech_dds/blob/master/lab4/task22.sql.
3. https://github.com/EmptyShadow/eltech_dds/blob/master/lab4/task23.sql.
4. https://github.com/EmptyShadow/eltech_dds/blob/master/lab4/task24.sql.