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

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

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

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

parameter_name data_type OUTPUT

Хранимая процедура может иметь много выходных параметров. Кроме того, выходные параметры могут быть в любом допустимом типе данных, например, целое число, дата и переменный символ.

Например, следующая хранимая процедура находит продукты по модельному году и возвращает количество продуктов через выходной параметр @product_count:

CREATE PROCEDURE uspFindProductByModel(
    @model_year SMALLINT,
    @product_count INT OUTPUT
) AS
BEGIN
    SELECT 
        product_name,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;
    SELECT @product_count = @@ROWCOUNT;
END;

В этой хранимой процедуре:

Сначала мы создали выходной параметр с именем @product_count для хранения количества найденных продуктов:

@product_count INT OUTPUT

Во-вторых, после оператора SELECT мы присвоили параметру @product_count количество строк, возвращаемых запросом(@@ROWCOUNT).

Обратите внимание, что @@ROWCOUNT — это системная переменная, которая возвращает количество строк, считанных предыдущим оператором.

После выполнения приведенного выше оператора CREATE PROCEDURE хранимая процедура uspFindProductByModel компилируется и сохраняется в каталоге базы данных.

Если все в порядке, SQL Server выдает следующий вывод:

Commands completed successfully.

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

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

• Сначала объявите переменные для хранения значений, возвращаемых выходными параметрами.
• Во-вторых, используйте эти переменные при вызове хранимой процедуры.

Например, следующий оператор выполняет хранимую процедуру uspFindProductByModel:

DECLARE @count INT;
EXEC uspFindProductByModel
    @model_year = 2018,
    @product_count = @count OUTPUT;
SELECT @count AS 'Number of products found';

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

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

Сначала объявим переменную @count для хранения значения выходного параметра хранимой процедуры:

DECLARE @count INT;

Затем выполните хранимую процедуру uspFindProductByModel и передайте параметры:

EXEC uspFindProductByModel 
     @model_year = 2018, 
     @product_count = @count OUTPUT;

В этом операторе model_year — 2018, а переменная @count присваивает значение выходного параметра @product_count.

Вы можете вызвать хранимую процедуру uspFindProductByModel следующим образом:

EXEC uspFindProductByModel 2018, @count OUTPUT;

Обратите внимание: если вы забудете указать ключевое слово OUTPUT после переменной @count, переменная @count будет иметь значение NULL.

Наконец, покажите значение переменной @count:

SELECT @count AS 'Number of products found';

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