SQL SERVER — входной и выходной параметр для динамического SQL

Опубликовано: 8 Января, 2023

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

Параметр, значение которого задается в хранимой процедуре/функциональном модуле, называется входным параметром . Значение параметра IN фиксировано; его нельзя изменить или переназначить внутри модуля.

Параметр, значение которого отправляется из хранимой процедуры/функционального модуля обратно в вызывающий блок PL/SQL, называется выходным параметром. В качестве выходного параметра должна использоваться переменная, а не константа. Его можно найти только в левой части назначения модуля. Вне тела модуля вы не можете указать значение по умолчанию для параметра OUT.

Иногда администраторы баз данных SQL оказываются в ситуации, когда они не помнят синтаксис какого-либо запроса и выполняют поиск в Интернете. Ниже приведены три примера. Каждый пример будет содержать вместе динамический SQL и параметры ввода, вывода и ввода/вывода.

Для определения входных или выходных параметров используйте ключевое слово «DECLARE», за которым следует символ «@» имени переменной «тип данных».

Пример:

DECLARE @inPar INT = 345;
DECLARE @outPar INT;

Создадим и посмотрим таблицу:

Запрос:

Create table TestOrders (OId int, PersonId int) ;
insert into TestOrders values(2, 345), (3, 657), (4, 789) ;
SELECT TOP 1000 [OId]   ,[PersonId] FROM [TestOrders];

Выход:

Входной параметр:

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

Пример входного параметра

inPar — входной параметр

DECLARE @inPar INT = 345;
DECLARE @test NVARCHAR(MAX) = N"SELECT TOP 1 [OId]
FROM TestOrders WHERE PersonId = @id_in";
EXEC sys.sp_executesql @test, N"@id_in INT", @inPar;
GO

Выход:

Выходной параметр:

Параметр, значение которого отправляется из хранимой процедуры/функционального модуля обратно в вызывающий блок PL/SQL, называется выходным параметром. В качестве параметра OUT должна использоваться переменная, а не константа.

Пример:

outPar — выходной параметр

DECLARE @outPar INT;
DECLARE @test NVARCHAR(MAX) = N"SELECT TOP 1 @id_out = [OId]
FROM TestOrders WHERE PersonId = 345";
EXEC sys.sp_executesql @test, N"@id_out INT OUT", @outPar OUT;
SELECT @outPar OutParValue
GO

Выход:

Пример параметра ввода/вывода:

DECLARE @inPar INT = 345;
DECLARE @outPar INT;
DECLARE @test NVARCHAR(MAX) = N"SELECT TOP 1 @id_out = [OId]
FROM [TestOrders] WHERE [PersonId] = @id_in";
EXEC sys.sp_executesql @test, N"@id_in INT, @id_out INT OUT",
@inPar, @outPar OUT;
SELECT @outPar OutParValue
GO

Выход:

SQL