Часть 3-8. Создание и использование хранимых процедур Упражнение 1 - Создание хранимой процедуры без параметров

1. Создать хранимую процедуруGetDidcounts в схеме Sales, которая извлекает следующие столбцы из таблицы Sales.SpecialOffer: Description, DiscountPct, Type, Category, StartDate, EndDate, MinQty, MaxQty. Процедура должна возвращать все строки, отсортированные по параметрам StartDate

и EndDate.

Рис. 1 Создание процедуры без параметров

2. Для проверки хранимой процедуры введём команду:

Рис 2. Проверка

Упражнение 2 – создание хранимой процедуры с параметром

1. Создать хранимую процедуру GetDidcountsForCategory в схеме Sales. Эта процедура должна иметь входной параметр @Category, имеющий тип данных и принимающий до 50 символов. Она должна извелкать те же столбцы, что и запрос в процедуре GetDidcounts, но фильтровать строки на основе парметра @Category.

Рис. 3

2. Для проверки хранимой процедуры:

Рис. 4 Проверка

Упражнение 3 – создание хранимой процедуры с параметрами и значениями по умолчанию

1. В схеме создать хранимую процедуру GetDidcountsForCategoryAndDate. Эта процедура должна иметь входной параметр, как и процедура GetDidcountsForCategory, но включает дополнительный входной параметр @DateToCheck datetime

GO

CREATE PROCEDURE Sales.GetDidcountsForCategoryAndDate

@Category NVARCHAR(50),

@DateToCheck datetime = NULL

AS

IF @DateToCheck IS NULL

BEGIN

SET @DateToCheck=GateDate()

SELECT Description, DiscountPct, Type, Category, StartDate, EndDate, MinQty, MaxQty

FROM Sales.SpecialOffer

WHERE Category=@Category, EndDate=@DateToCheck

END

ELSE

BEGIN

SELECT Description, DiscountPct, Type, Category, StartDate, EndDate, MinQty, MaxQty

FROM Sales.SpecialOffer

ORDER BY @Category, @DateToCheck

END

GO

2. Для проверки хранимой процедуры:

EXEC Sales.GetDidcountsForCategoryAndDate 'Reseller'

Рис. 5

3. Проверить вариант, когда указываются оба параметра

DECLARE @DateToChek datetime

SET @DateToCheck = DateAdd(month, 1, GetDate())

EXEC Sales.GetDidcountsForCategoryAndDate 'Reseller', @DateToChek.

Рис.6

Часть 3-9. Создание udf

Цель работы: Научиться писать и применять функции, определяемые пользователем (UDF). В лабораторной работе используются две базы данных AdventureWorks и AdventureWorksDW. Перед выполнением работы откройте файл InitializeData.sql и выполните его.

Упражнение 1 - Создание скалярной функции

1. Создадим определяемую пользователем скалярную функцию Sales.GetMaximumDiscountForCategory, которая находит максимальный процент скидки, доступный на данный момент для конкретной категории. Создадим параметр @Categorynvarchar(50) для ограничения результатов на основе категории и использовать функциюGETDATE() для ограничения строк на основе доступности скидки на данный момент в диапазонеStartDateиEndDate.

CREATE FUNCTION Sales.GetMaximumDiscountForCategory (@Category nvarchar(50))

RETURNS smallmoney

AS

BEGIN

DECLARE @Max smallmoney

SELECT @Max = MAX(DiscountPct)

FROM Sales.SpecialOffer

WHERE Category = @Category

AND GetDate() Between StartDate AND EndDate

GROUP BY Category

RETURN @Max

END

Рис. 1. Создание скалярной функции

2. Для проверки функции введём следующую команду

SELECT Sales.GetMaximumDiscountForCategory('Reseller'):

Рис 2. Результат проверки

Убедились, что функция работает корректно.