Параметры хранимой процедуры SQL Server

В предыдущем уроке вы узнали, как создать простую хранимую процедуру, которая оборачивает оператор SELECT. Когда вы вызываете эту хранимую процедуру, она просто выполняет запрос и возвращает набор результатов.

В этом уроке мы расширим хранимую процедуру, которая позволяет передавать ей одно или несколько значений. Результат хранимой процедуры будет меняться в зависимости от значений параметров.

Создание хранимой процедуры с одним параметром

Следующий запрос возвращает список продуктов из таблицы продуктов в образце базы данных :

SELECT
    product_name,
    list_price
FROM 
    production.products
ORDER BY
    list_price;

Вы можете создать хранимую процедуру, которая оборачивает этот запрос, используя оператор CREATE PROCEDURE:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;

Однако на этот раз мы можем добавить параметр в хранимую процедуру, чтобы найти продукты, цены по прейскуранту которых превышают входную цену:

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;

В этом примере:

• Сначала мы добавили параметр с именем @min_list_price в хранимую процедуру uspFindProducts. Каждый параметр должен начинаться со знака @. Ключевые слова AS DECIMAL указывают тип данных параметра @min_list_price. Параметр должен быть заключен в открывающие и закрывающие скобки.
• Во-вторых, мы использовали параметр @min_list_price в предложении WHERE оператора SELECT, чтобы отфильтровать только те продукты, цены по прейскуранту которых больше или равны @min_list_price.

Выполнение хранимой процедуры с одним параметром

Чтобы выполнить хранимую процедуру uspFindProducts, вы передаете ей аргумент следующим образом:

EXEC uspFindProducts 100;

Хранимая процедура возвращает все продукты, цены по прейскуранту которых больше или равны 100.

Если изменить аргумент на 200, вы получите другой набор результатов:

EXEC uspFindProducts 200;

Создание хранимой процедуры с несколькими параметрами

Хранимые процедуры могут принимать один или несколько параметров. Параметры разделяются запятыми.

Следующий оператор изменяет хранимую процедуру uspFindProducts, добавляя в нее еще один параметр с именем @max_list_price:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;

После успешного изменения хранимой процедуры вы можете выполнить ее, передав два аргумента: один для @min_list_price, а другой для @max_list_price:

EXECUTE uspFindProducts 900, 1000;

Ниже показан результат:

Использование именованных параметров

В случае, если хранимые процедуры имеют несколько параметров, лучше и понятнее выполнять хранимые процедуры с использованием именованных параметров.

Например, следующий оператор выполняет хранимую процедуру uspFindProducts, используя именованные параметры @min_list_price и @max_list_price:

EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000;

Результат хранимой процедуры тот же, однако выражение более очевидно.

Создание текстовых параметров

Следующий оператор добавляет параметр @name как параметр символьной строки в хранимую процедуру.

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

В предложение WHERE оператора SELECT мы добавили следующее условие:

product_name LIKE '%' + @name + '%'

При этом хранимая процедура возвращает продукты, цены на которые находятся в диапазоне минимальных и максимальных цен, а названия продуктов также содержат фрагмент текста, который вы передаете.

После успешного изменения хранимой процедуры ее можно выполнить следующим образом:

EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000,
    @name = 'Trek';

В этом выражении мы использовали хранимую процедуру uspFindProducts для поиска продуктов, цены на которые находятся в диапазоне от 900 до 1000, а их названия содержат слово Trek.

На следующем рисунке показан результат:

Создание необязательных параметров

При выполнении хранимой процедуры uspFindProducts необходимо передать все три аргумента, соответствующие трем параметрам.

SQL Server позволяет указывать значения по умолчанию для параметров, чтобы при вызове хранимых процедур можно было пропустить параметры со значениями по умолчанию.

См. следующую хранимую процедуру:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = 999999
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

В этой хранимой процедуре мы назначили 0 в качестве значения по умолчанию для параметра @min_list_price и 999 999 в качестве значения по умолчанию для параметра @max_list_price.

После компиляции хранимой процедуры вы можете выполнить ее, не передавая аргументы параметрам @min_list_price и @max_list_price:

EXECUTE uspFindProducts 
    @name = 'Trek';

В этом случае хранимая процедура использовала 0 для параметра @min_list_price и 999 999 для параметра @max_list_price при выполнении запроса.

Параметры @min_list_price и @max_list_price называются необязательными параметрами.

Конечно, вы также можете передавать аргументы в необязательные параметры. Например, следующий оператор возвращает все продукты, чьи цены в прайс-листе больше или равны 6000, а названия содержат слово Trek:

EXECUTE uspFindProducts 
    @min_list_price = 6000,
    @name = 'Trek';

Использование NULL в качестве значения по умолчанию

В хранимой процедуре uspFindProducts мы использовали 999 999 в качестве максимальной цены по прейскуранту по умолчанию. Это ненадежно, поскольку в будущем у вас могут быть продукты с ценами по прейскуранту, которые будут выше.

Типичный способ избежать этого — использовать NULL в качестве значения по умолчанию для параметров:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = NULL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
       (@max_list_price IS NULL OR list_price <= @max_list_price) AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

В предложении WHERE мы изменили условие для обработки значения NULL для параметра @max_list_price:

(@max_list_price IS NULL OR list_price <= @max_list_price) 

Следующий оператор выполняет хранимую процедуру uspFindProducts для поиска продуктов, цены по прейскуранту которых больше или равны 500, а названия содержат слово Haro.

EXECUTE uspFindProducts 
    @min_list_price = 500,
    @name = 'Haro';

В этом уроке вы узнали, как создавать и выполнять хранимые процедуры с одним или несколькими параметрами. Вы также узнали, как создавать необязательные параметры и использовать NULL в качестве значений по умолчанию для параметров.