Входной параметр хранимой процедуры SQL Server, выходной параметр и возвращаемое значение

Данный материал является переводом оригинальной статьи "MSSQLTips : Rick Dobson : SQL Server Stored Procedure Input Parameter, Output Parameter and Return Value".

Предположим, как начинающий разработчик SQL Server, вы недавно научились размещать сценарии T-SQL в шаблоне хранимой процедуры, чтобы код сценария можно было легко повторно использовать. Однако вы желаете повысить свой уровень владения хранимыми процедурами для определения и использования входных и выходных параметров. Кроме того вы хотите найти пример кода, который расширит понимание того, как указывать и использовать значения кода возврата.

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

Базовые положения заключаются в том, что:

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

• Коды возврата из оператора return в хранимой процедуре могут достигать двух целей. Во-первых, каждый оператор return в хранимой процедуре может принудительно выполнить выход из хранимой процедуры всякий раз, когда он встречается. Во-вторых, целочисленные значения, связанные с операторами return, могут указывать место, из которого хранимая процедура переходит к вызывающему сценарию T-SQL.

Примеры указания и использования входных параметров для хранимых процедур SQL Server

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

Блок кода начинается с установки базы данных CodeModuleTypes по умолчанию. Вы можете использовать любую другую базу данных по своему усмотрению.

Перед вызовом оператора create proc, код удаляет предыдущую версию хранимой процедуры, если таковая существует. Оператор drop proc ссылается на хранимую процедуру по имени схемы (dbo), в которой она находится, в качестве квалификатора для имени объекта хранимой процедуры (uspMyThirdStoredProcedure).

После имени хранимой процедуры в операторе create proc указываются два входных параметра. Каждая спецификация входного параметра состоит из имени параметра, за которым следует тип данных. Имена параметров должны начинаться с символа @.

Параметр @SalesPersonID имеет тип данных int. Это целое число, обозначающее продавца.

Параметр @Sales_Yr также имеет тип данных int. Это четырехзначное целое число, обозначающее год размещения заказа.

После ключевого слова as оператор select обозначает набор результатов на основе пары вложенных запросов.

Внутренний запрос объединяет таблицы SalesOrderHeader, SalesPerson и Person. Поля возвращаются как из таблиц SalesOrderHeader, так и из таблиц Person. Левое соединение между таблицей SalesOrderHeader и таблицей SalesPerson позволяет пометить (как нулевое значение) любые значения поля SalesPersonID в таблице SalesOrderHeader, которых нет в таблице SalesPerson, как значение BusinessEntityID. Левое соединение между таблицей Person и таблицей SalesPerson также помечает значения полей имени и фамилии как пустые, если значение поля SalesPerson BusinessEntityID не соответствует какому-либо значению поля BusinessEntityID из таблицы Person.

• SalesPersonID - это идентификатор целого числа для продавца.
• Поля FirstName и LastName взяты из таблицы Person и содержат имя и фамилию продавца.
• SaleOrderID - это целочисленный идентификатор заказа.
• Sales_Yr - поле, вычисляемое на основе года для заказа.
• TotalDue - это общая сумма продаж, связанная с заказом.

Внешний запрос выполняет три основные функции.

• Во-первых, он группирует заказы по SalesPersonID, FirstName, LastName и Sales_Yr.
• Во-вторых, он вычисляет два агрегированных поля для каждой группы:
  • количество заказов, сгруппированных по годам, для каждого продавца
• • сумма продаж сгруппированных по годам для каждого продавца
• В-третьих, он извлекает конкретную строку из набора заказов, сгруппированных по продавцу и Sales_Yr. В условии наличия указано, что SalesPersonID должен быть равен @SalesPersonID, а Sale_yr должен быть равен @Sales_Yr.

use CodeModuleTypes
go   
-- conditionally drop a stored proc
if object_id('dbo.uspMyThirdStoredProcedure') is not null 
     drop proc dbo.uspMyThirdStoredProcedure
go   
-- create dbo.uspMyThirdStoredProcedure stored proc so that it accepts two input parameters
create proc dbo.uspMyThirdStoredProcedure
   @SalesPersonID int
  ,@Sales_Yr int
as   
-- count and sum of total orders 
-- by SalesPersonID, FirstName, LastName, and Sale_Yr
select 
   SalesPersonID
  ,FirstName
  ,LastName
  ,Sale_Yr
  ,count(SalesOrderID) Total_Orders
  ,sum(TotalDue) Total_Sales
from
(
  -- list of SalesOrderIDs with SalesPersonID
  select
     SalesPerson.BusinessEntityID SalesPersonID
    ,[Person].FirstName
    ,[Person].LastName
    ,[SalesOrderHeader].[SalesOrderID]
    ,[SalesOrderHeader].[OrderDate]
    ,[SalesOrderHeader].[TotalDue]
    ,YEAR([SalesOrderHeader].[OrderDate]) Sale_Yr
  from [AdventureWorks2014].[Sales].[SalesOrderHeader]
  left join [AdventureWorks2014].[Sales].[SalesPerson]
    on SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
  left join [AdventureWorks2014].[Person].[Person]
    on Person.BusinessEntityID = SalesPerson.BusinessEntityID
  where OnlineOrderFlag != 1 -- exclude online sale orders that have no salesperson
) for_total_sales_by_salesperson
group by
   SalesPersonID
  ,FirstName
  ,LastName
  ,Sale_Yr
having 
  SalesPersonID = @SalesPersonID
  and Sale_Yr = @Sales_Yr

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

Каждый из первых трех операторов exec заканчивается ключевым словом go. Это ключевое слово заставляет каждый оператор exec работать в отдельном пакете, так что ошибка в любом одном пакете не приводит к обходу других последующих операторов. За четвертым оператором exec нет кода. Все четыре оператора exec пытаются присвоить два одинаковых значения параметрам @SalesPersonID и @Sales_Yr.

• Первый оператор exec показывает, как присвоить значения по позициям обоим параметрам. Во-первых, 274 присваивается как значение @SalesPersonID. Далее в качестве значения @Sales_Yr присваивается 2014 год.
• Второй оператор exec показывает, как назначать значения по имени для обоих параметров.
• Третий оператор exec иллюстрирует смешанное правило назначения с позицией, используемой для @SalesPersonID, и именем, используемым для @Sales_Yr.
• Четвертый оператор exec завершился синтаксической ошибкой. В этом случае, параметру @SalesPersonID сначала присваивается значение по имени, после чего следует попытка присвоить значение @Sales_Yr по позиции, то есть без использования имени. Ошибка возникает из-за того, что после присвоения значения любому входному параметру по имени, все остальные входные параметры также должны иметь свое значение, указанное по имени.

-- assign @SalesPersonID and @Sales_Yr input parameters based on position
exec dbo.uspMyThirdStoredProcedure 274, 2014
go   
-- assign @SalesPersonID and @Sales_Yr input parameters
-- with parameter name, assignment operator (=), and its value
exec dbo.uspMyThirdStoredProcedure @SalesPersonID = 274, @Sales_Yr = 2014
go   
-- assign input parameters with first parameter un-named
-- and second parameter named
-- these assignments succeed
exec dbo.uspMyThirdStoredProcedure 274, @Sales_Yr = 2014
go   
-- assign input parameters with first parameter named
-- and second parameter un-named
-- these assignments fail
exec dbo.uspMyThirdStoredProcedure @SalesPersonID = 274, 2014

Вот наборы результатов из первых трех операторов exec. Панели появляются по порядку для первого, второго и третьего операторов exec. Каждая панель имеет одинаковое значение SalesPersonID и одно и то же значение Sale_Yr. Независимо от того, установлены ли значения входных параметров по позиции, имени параметра или комбинации позиции first и name second, результат запроса из хранимой процедуры будет одинаковым.

Как уже упоминалось, четвертый оператор exec завершился неудачно. Ниже показано сообщение об ошибке на вкладке "Messages". Вы можете видеть, что номер ошибки - 119. Связанное сообщение об ошибке указывает, что второй и последующие входные параметры (если их больше двух) должны быть переданы, сначала указав имя, а затем значение(я) для второго и последующих входных параметров. Этот метод соответствует третьему оператору exec. Однако вы также можете использовать любой из двух предыдущих форматов операторов exec для обозначения значений параметров во время выполнения.

Msg 119, Level 15, State 1, Line 75
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Примеры указания и использования выходных параметров для хранимых процедур SQL Server

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

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

• Новая версия хранимой процедуры возвращает один набор значений выходных параметров с общим количеством заказов и общей суммой продаж для продавца, идентифицированного по фамилии. В предыдущем примере был получен такой же вывод, но из набора строк с одной строкой вместо набора из трех скалярных значений из выходных параметров.
• Оператор create proc включает пять спецификаций параметров. Объявления параметров появляются после имени хранимой процедуры и перед ключевым словом as.
• Три имени выходных параметров: @LastName, @Total_Orders и @Total_Sales_Amount.
• Каждый выходной параметр должен сопровождаться одним из двух ключевых слов: output или out.
• Сохраненная процедура присваивает значение каждому выходному параметру во внешнем запросе.
• @SalesPersonID и @Sales_Yr - входные параметры. Оба параметра служат в качестве значений критериев для подсчета заказов на продажу и суммирования сумм продаж для конкретного продавца в течение определенного года.
• После ключевого слова as следует код T-SQL для заполнения выходных параметров с помощью пары вложенных запросов, которые полагаются на значения входных параметров, чтобы помочь заполнить выходные параметры.
• Внутренний запрос генерирует список заказов на продажу продавцом с другими соответствующими данными, такими как SalesOrderID для идентификации отдельных заказов и TotalDue для обозначения окончательной суммы для отдельных заказов на продажу.
• Во внутреннем запросе также перечислены заказы на продажу с датой заказа, чтобы определить год, в течение которого была совершена продажа, а также SalesPersonID для обозначения продавца, совершающего продажу.
• Внешний запрос содержит функцию подсчета для подсчета количества значений SalesOrderID и функцию суммы для поля TotalDue для агрегирования сумм продаж по заказам на продажу.
• Предложение Have во внешнем запросе дополнительно ограничивает вывод, поэтому суммарные значения продаж (SalesOrderID и TotalDue) относятся к году @Sales_Yr и продавцу @SalesPersonID.

-- conditionally drop a stored proc
if object_id('dbo.uspMyThirdStoredProcedure') is not null 
     drop proc dbo.uspMyThirdStoredProcedure
go   create proc dbo.uspMyThirdStoredProcedure
   @SalesPersonID int
  ,@Sales_Yr int
  ,@LastName nvarchar(50) output
  ,@Total_Orders int out
  ,@Total_Sales_Amount money out
as   
-- count and sum of total orders by SalesPersonID with LastName
select 
   @LastName = LastName
  ,@Total_Orders = count(SalesOrderID) 
  ,@Total_Sales_Amount = sum(TotalDue) 
from
(
  
-- list of SalesOrderIDs with SalesPersonID
  select
     SalesPerson.BusinessEntityID SalesPersonID
    ,[Person].FirstName
    ,[Person].LastName
    ,[SalesOrderHeader].[SalesOrderID]
    ,[SalesOrderHeader].[OrderDate]
    ,[SalesOrderHeader].[TotalDue]
    ,YEAR([SalesOrderHeader].[OrderDate]) Sale_Yr
  from [AdventureWorks2014].[Sales].[SalesOrderHeader]
  left join [AdventureWorks2014].[Sales].[SalesPerson] 
    on SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
  left join [AdventureWorks2014].[Person].[Person]
    on Person.BusinessEntityID = SalesPerson.BusinessEntityID
  where OnlineOrderFlag != 1 -- exclude online sale orders that have no salesperson
) for_total_sales_by_salesperson
group by
   SalesPersonID
  ,FirstName
  ,LastName
  ,Sale_Yr
having 
  SalesPersonID = @SalesPersonID
  and Sale_Yr = @Sales_Yr
go

Следующий сценарий показывает T-SQL для отображения значений выходных параметров вместе с входным параметром @Sale_Yr. Значения выходных параметров назначаются в uspMyThirdStoredProcedure. Однако SQL Server требует, чтобы вы назначили выходной параметр локальным переменным, прежде чем вы сможете ссылаться на них вне хранимой процедуры. В приведенном ниже примере используются одинаковые имена для выходных параметров и соответствующих им локальных переменных.

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

• Объявите локальную переменную для входного параметра, на который вы хотите ссылаться после запуска хранимой процедуры. Присвойте значение локальной переменной для входного параметра либо в операторе объявления, либо в отдельном операторе набора. Также используйте тот же оператор объявления, чтобы назначить локальные переменные для получения значений выходных параметров в операторе exec для запуска хранимой процедуры.
• Вызовите оператор exec с локальными переменными для значений выходных параметров, а также любых входных параметров, на которые вам нужно будет ссылаться локально.
• Встроенные операторы присваивания в операторе exec могут фиксировать выходные параметры в локальных переменных.
• Используйте локальные переменные для одного или нескольких входных параметров так же, как любые константы, которые вы бы использовали для присвоения значений входным параметрам.
• После завершения хранимой процедуры и возврата выходных параметров в локальные переменные в операторе exec вы можете использовать локальные переменные для входного параметра точно так же, как локальную переменную со значениями выходных параметров.

Вот обзор блока кода ниже, который демонстрирует эти рекомендации.

• Оператор declare определяет четыре локальные переменные.
• Три из них (@LastName, @Total_Orders, @Total_Sales_Amount) предназначены для хранения значений выходных параметров из хранимой процедуры.
• Четвертая локальная переменная @Sales_Yr предназначена для хранения значения входного параметра. Последующий оператор set присваивает значение 2014 локальной переменной @Sales_Yr.
• Затем вызывается оператор exec с локальной переменной для одного из двух входных параметров и локальной переменной для каждого выходного параметра.
• Имя @SalesPersonID обозначает первый входной параметр. Этот параметр помогает ограничить вывод хранимой процедуры, так что столбец BusinessEntityID в таблице SalesPerson должен соответствовать значению параметра @SalesPersonID. Приведенный ниже код передает значение 274 в @SalesPersonID, что указывает на продавца по имени Stiven Jiang.
• Имя @Sales_Yr обозначает второй входной параметр. Параметр помогает ограничить вывод хранимой процедуры на основе значений столбца Sale_Yr из набора результатов внутреннего запроса. Входному параметру присваивается значение локальной переменной @Sales_Yr (2014).
• Локальной переменной @LastName присваивается значение выходного параметра @LastName в третьем назначении в операторе exec. Значение этого параметра - Jiang.
• Локальной переменной @Total_Orders назначается выходной параметр @Total_Orders в четвертом назначении в операторе exec. Значение этого параметра - 8.
• Локальной переменной @Total_Sales_Amount назначается выходной параметр @Total_Sales_Amount в пятом назначении в операторе exec. Значение этого параметра в примере - 201288.5196.
• Оператор select в конце скрипта отображает три значения выходных параметров, переданных в локальные переменные, а также значение локальной переменной, переданное во входной параметр.

-- declare local variables for output parameters
declare
 @lastName nvarchar(50)
,@Sales_Yr int 
,@Total_Orders int
,@Total_Sales_Amount money   
-- set local variable for subsequent assignment to input parameter in exec statement
-- and for local use in select statement after exec statement
set @Sales_Yr = 2014   
-- invoke stored proc and assign output parameters to local variables
exec dbo.uspMyThirdStoredProcedure 
 @SalesPersonID = 274
,@Sales_Yr = @Sales_Yr
,@LastName = @LastName output
,@Total_Orders = @Total_Orders out
,@Total_Sales_Amount = @Total_Sales_Amount out   
-- display local variables with output parameters
select 
 @LastName LastName
,@Sales_Yr Sales_Year
,@Total_Orders Total_Orders
,@Total_Sales_Amount Total_Sales_Amount

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

Примеры указания и использования кодов возврата для хранимых процедур SQL Server

Следующий сценарий генерирует третью свежую копию uspMyThirdStoredProcedure в схеме dbo. Хранимая процедура имеет операторы if, управляющие доступом к одному из трех блоков begin ... end. Оператор return в каждом блоке begin … end завершает сохраненную процедуру со значением кода возврата 1, 2 или 3. Внутри каждого блока вы также можете добавить код для выполнения оператора select, вставить строку значений в таблицу, обновить набор значений в таблице или что-то еще, что вы хотите выполнить с помощью T-SQL. Значение кода возврата из хранимой процедуры указывает путь, пройденный в хранимой процедуре.

В uspMyThirdStoredProcedure доступ к одному из трех блоков begin … end зависит от значения входного параметра @SalesPersonID.

• Значение @SalesPersonID меньше 274 приводит к значению кода возврата, равному 1.
• Значение @SalesPersonID больше 290 приводит к значению кода возврата 2.
• Значения @SalesPersonID больше или равные 274 и меньше или равные 290 приводят к значению кода возврата 3.

-- conditionally drop a stored proc
if object_id('dbo.uspMyThirdStoredProcedure') is not null 
     drop proc dbo.uspMyThirdStoredProcedure
go   
-- simple control flow example with return codes 
create proc dbo.uspMyThirdStoredProcedure 
@SalesPersonID int 
as   
   -- control flow code 
   if @SalesPersonID < 274 
   begin 
      
      -- place here code to execute when @SalesPersonID < 274 
      return (1) 
   end;   if @SalesPersonID > 290 
   begin 
      
      -- place here code to execute when @SalesPersonID > 290 
      return (2) 
   end;   begin 
      
      -- place here code to execute when 
      -- @SalesPersonID >= 274 and 
      -- @SalesPersonID <= 290 
      return (3) 
end;

Получить значение кода возврата можно в два этапа. Во-первых, вам нужно объявить локальную переменную, такую как @return_status, в следующем скрипте, в которую нужно передать значение кода возврата из хранимой процедуры. Во-вторых, вам необходимо присвоить значение кода возврата из оператора exec хранимой процедуры локальной переменной.

Приведенный ниже сценарий иллюстрирует синтаксис для выполнения этих шагов для трех разных значений @SalesPersonID; каждое представленное значение входного параметра приводит к тому, что внутри хранимой процедуры используется другой путь. Встроенный оператор присваивания внутри оператора exec для uspMyThirdStoredProcedure передает значение кода возврата из хранимой процедуры в локальную переменную (@return_status). Наконец, пара операторов select повторяет значения входного параметра (@SalesPersonID) и значение кода возврата (@return_status).

-- declare local variable for return code value
declare @return_status int, @SalesPersonID int;   
-- @SalesPersonID = 273 results in a Return Status of 1
set @SalesPersonID = 273
exec @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
select @SalesPersonID input_parameter
select 'Return Status' = @return_status     
-- @SalesPersonID = 291 results in a Return Status of 2
set @SalesPersonID = 291
exec @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
select @SalesPersonID input_parameter
select 'Return Status' = @return_status   
-- @SalesPersonID = 274 results in a Return Status of 3
set @SalesPersonID = 274
exec @return_status = dbo.uspMyThirdStoredProcedure @SalesPersonID;
select @SalesPersonID input_parameter
select 'Return Status' = @return_status

Вот как выглядит результат выполнения предыдущего скрипта.

• Когда значение входного параметра равно 273, значение кода возврата равно 1.
• Если значение входного параметра равно 291, значение кода возврата равно 2.
• Когда значение входного параметра равно 274, значение кода возврата равно 3.

Следующие шаги

• Вы можете опробовать примеры кода из этой статьи на компьютере с тестовой базой данных AdventureWorks2014. Инструкции по загрузке этой БД доступны в статье "Install Your Own Copy of the SQL Server AdventureWorks2014 Database".
• Затем скопируйте скрипт(ы), который вы хотите протестировать и изменить. Убедитесь, что скопированный код дает допустимые результаты для базы данных AdventureWorks2014.
• Наконец, измените сценарий для работы в другой базе данных по вашему выбору, чтобы начать создание и выполнение хранимых процедур с вашими сценариями в ваших базах данных.