Возврат данных из сохраненной процедуры
Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)
Существует три способа возврата данных из процедуры в вызывающую программу: результирующие наборы, параметры вывода и коды возврата. В этой статье приведены сведения по всем трем способам.
Возврат данных с помощью результирующих наборов
Если вы включаете инструкцию SELECT в текст хранимой процедуры (но не select ... INTO или INSERT ... SELECT), строки, указанные инструкцией SELECT, будут отправляться непосредственно клиенту. Для крупных результирующих наборов выполнение хранимой процедуры не перейдет к следующей инструкции, пока результирующий набор не будет полностью передан клиенту. Для небольших результирующих наборов результаты копируются для возврата клиенту и выполнение продолжится. Если при выполнении хранимой процедуры запускается несколько таких инструкций SELECT, клиенту отправляется несколько результирующих наборов. Такое поведение также применяется к вложенным пакетам Transact-SQL, вложенным хранимым процедурам и пакетам Transact-SQL верхнего уровня.
Примеры возврата данных с помощью результирующего набора
В следующих примерах используется AdventureWorks2022
пример базы данных. В этом примере приведена хранимая процедура, которая возвращает значения LastName
и SalesYTD
для всех строк SalesPerson
, которые также отображаются в представлении vEmployee
.
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
AS
SET NOCOUNT ON;
SELECT LastName, SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO
Возврат данных с помощью выходного параметра
Процедура может возвращать текущее значение параметра вызываемой программе при завершении работы при указании ключевого слова OUTPUT для параметра в определении процедуры. Чтобы сохранить значение параметра в переменной, которая может быть использована в вызываемой программе, при выполнении процедуры вызываемая программа должна использовать ключевое слово OUTPUT. Дополнительные сведения о типах данных, которые можно использовать в качестве выходных параметров, см. в статье CREATE PROCEDURE (Transact-SQL).
Примеры выходных параметров
Следующий пример представляет процедуру с входным и выходным параметрами. Параметр @SalesPerson
получает входное значение, указанное вызывающей программой. Инструкция SELECT использует значение, переданное входному параметру для получения верного значения SalesYTD
. Инструкция SELECT также присваивает это значение выходному параметру @SalesYTD
, который возвращает значение вызывающей программе при завершении процедуры.
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN;
GO
Следующий пример вызывает процедуру, созданную в первом примере, и сохраняет выходной параметр @SalesYTD
, возвращаемый из вызываемой @SalesYTDBySalesPerson
процедуры в переменную.
Пример.
- Объявляет переменную
@SalesYTDBySalesPerson
для получения выходного значения процедуры. - Выполняет процедуру
Sales.uspGetEmployeeSalesYTD
, указывающую фамилию для входного параметра. Сохраните выходное значение в переменной@SalesYTDBySalesPerson
. - Вызывает метод PRINT для отображения значения, сохраненного в
@SalesYTDBySalesPerson
.
DECLARE @SalesYTDBySalesPerson money;
EXECUTE Sales.uspGetEmployeeSalesYTD
N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
PRINT 'Year-to-date sales for this employee is ' +
CONVERT(varchar(10),@SalesYTDBySalesPerson);
GO
Входные значения также могут быть указаны для выходных параметров при выполнении процедуры. Это позволяет хранимой процедуре получать значение из вызываемой программы, изменять его или выполнять операции с этим значением, а затем возвращать новое значение вызываемой программе. В предыдущем примере переменной @SalesYTDBySalesPerson
может быть присвоено значение прежде, чем программа вызовет процедуру Sales.uspGetEmployeeSalesYTD
. Эта инструкция передает значение переменной @SalesYTDBySalesPerson
выходному параметру @SalesYTD
. Далее в тексте процедуры значение можно использовать для вычислений, формирующих новое значение. Новое значение передается обратно из процедуры через выходной параметр, обновляя значение в переменной @SalesYTDBySalesPerson
при завершении процедуры. Часто это называется «возможностью передачи по ссылке».
Если при вызове процедуры указано ключевое слово OUTPUT для параметра, а параметр не определен при помощи OUTPUT в определении процедуры, выдается сообщение об ошибке. Но процедуру можно выполнить с выходными параметрами, не указывая OUTPUT при выполнении процедуры. Сообщение об ошибке не будет выдаваться, но нельзя будет использовать выходное значение в вызываемой программе.
Использование типа данных cursor в выходных параметрах
Процедуры Transact-SQL могут использовать тип данных курсора только для выходных параметров. Если тип данных cursor указан для параметра, как ключевое слово VARYING, так и ключевое слово OUTPUT должны быть указаны для этого параметра в определении процедуры. Параметр может быть указан только как выходной, однако если в объявлении параметра указано ключевое слово VARYING, типом данных должен быть cursor, при этом также следует указать ключевое слово OUTPUT.
Примечание.
Тип данных cursor не может быть связан с переменными приложения через интерфейсы API баз данных, таких как OLE DB, ODBC, ADO и DB-Library. Так как выходные параметры должны быть привязаны прежде, чем приложение сможет выполнить хранимую процедуру, процедуры с выходными параметрами типа cursor не могут быть вызваны из API базы данных. Эти процедуры можно вызывать из пакетов Transact-SQL, процедур или триггеров, только если переменная вывода курсора назначена локальной переменной курсора Transact-SQL.
Правила для выходных параметров курсора
Следующие правила относятся к выходным параметрам типа cursor при выполнении процедуры:
Для курсора последовательного доступа в результирующий набор курсора будут возвращены только строки с текущей позиции курсора до конца курсора. Текущая позиция курсора определяется при окончании выполнения процедуры. Например:
Курсор без прокрутки открывается в процедуре в результирующем наборе с именем
RS
100 строк.Процедура выбирает первые 5 строк результирующего набора
RS
.Процедура возвращает результат участнику.
Результирующий набор
RS
, возвращенный вызывающему объекту, состоит из строк с 6 по 100 из набораRS
, и курсор в вызывающем объекте размещен перед первой строкойRS
.
Для курсора последовательного доступа, если курсор позиционирован перед первой строкой после завершения хранимой процедуры, весь результирующий набор будет возвращен к вызывающему пакету, процедуре или триггеру. После возврата позиция курсора будет установлена перед первой строкой.
Для курсора последовательного доступа, если курсор позиционирован за концом последней строки после завершения хранимой процедуры, вызывающему пакету, процедуре или триггеру будет возвращен пустой результирующий набор.
Примечание.
Пустой результирующий набор отличается от значения NULL.
Для прокручиваемого курсора все строки в результирующем наборе будут возвращены к вызывающему пакету, процедуре или триггеру после выполнения процедуры. При возврате позиция курсора остается в позиции последней выборки, выполненной в процедуре.
Для любого типа курсора, если курсор закрыт, вызывающему пакету, процедуре или триггеру будет возвращено значение NULL. Это же произойдет в случае, если курсор присвоен параметру, но этот курсор никогда не открывался.
Примечание.
Закрытое состояние имеет значение только во время возврата. Например, можно при выполнении процедуры закрыть курсор, снова открыть его позже в процедуре и возвратить этот результирующий набор курсора в вызывающий пакет, процедуру или триггер.
Примеры выходных параметров курсора
В следующем примере создается процедура, указывающая выходной параметр с @CurrencyCursor
помощью типа данных курсора. Процедура затем будет вызвана из пакета.
Сначала следует создать процедуру, объявляющую и открывающую курсор для таблицы Currency
.
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.Currency;
OPEN @CurrencyCursor;
GO
Затем выполните пакет, который объявляет локальную переменную курсора, выполняет процедуру, присваивающую курсор локальной переменной, и затем выбирает строки из курсора.
USE AdventureWorks2022;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
Возврат данных с помощью кода возврата
Процедура может возвращать целочисленное значение, называемое кодом возврата, чтобы указать состояние выполнения процедуры. Код возврата для процедуры указывается при помощи инструкции RETURN. Как и выходные параметры, при выполнении процедуры код возврата необходимо сохранить в переменной, чтобы использовать это значение в вызывающей программе. Например, переменная назначения @result
типа int
используется для хранения кода возврата процедуры my_proc
:
DECLARE @result int;
EXECUTE @result = my_proc;
GO
Коды возврата часто применяются в блоках управления потоком процедур для присвоения кода возврата каждой из возможных ошибок. Функцию можно использовать @@ERROR
после инструкции Transact-SQL, чтобы определить, произошла ли ошибка во время выполнения инструкции. Перед введением TRY
//CATCH
THROW
обработки ошибок в коды возврата Transact-SQL иногда требуется определить успешность или сбой хранимых процедур. Хранимые процедуры всегда должны указывать на сбой с ошибкой (созданной при THROW
/RAISERROR
необходимости) и не полагаться на код возврата, чтобы указать на сбой. Кроме того, следует избегать использования кода возврата для возврата данных приложения.
Примеры кодов возврата
В следующем примере показана процедура usp_GetSalesYTD
с обработкой ошибок, устанавливающей специальные значения кода возврата для различных ошибок. В следующей таблице показано целое число, которое назначается процедурой каждой возможной ошибке, и соответствующее значение каждого числа.
Значения кодов возврата | Значение |
---|---|
0 | Выполнено успешно. |
1 | Требуемое значение параметра не указано. |
2 | Требуемое значение параметра не допустимо. |
3 | Произошла ошибка при получении значения продаж. |
4 | Найдено значение NULL для продаж данного менеджера. |
Этот пример создает процедуру с именем Sales.usp_GetSalesYTD
, которая делает следующее:
@SalesPerson
Объявляет параметр и задает значениеNULL
по умолчанию. Этот параметр предназначен для получения фамилии менеджера по продажам.- Проверяет параметр
@SalesPerson
.- Если
@SalesPerson
значение NULL, процедура выводит сообщение и возвращает возвращаемый код1
. - В противном случае, если параметр
@SalesPerson
не имеет значение NULL, процедура проверяет количество строк в таблицеHumanResources.vEmployee
с фамилией, имеющей значение@SalesPerson
. Если число равно нулю, процедура возвращает возвращаемый код2
.
- Если
- Запрашивает данные о продажах с начала года для менеджера по продажам с заданной фамилией и назначает полученное значение выходному параметру
@SalesYTD
. - Проверяет наличие ошибок SQL Server путем тестирования @@ERROR (Transact-SQL).
- Если
@@ERROR
значение равно нулю, процедура возвращает возвращаемый код3
. - Если
@@ERROR
значение равно нулю, процедура проверяет, имеет ли@SalesYTD
значение параметра ЗНАЧЕНИЕ NULL. Если не найдено год к дате продаж, процедура возвращает код4
возврата. - Если ни из предыдущих условий не задано значение true, процедура возвращает возвращаемый код
0
.
- Если
- Если достигнута последняя инструкция в хранимой процедуре, она вызывает хранимую процедуру рекурсивно без указания входного значения.
В конце примера предоставлен код для выполнения процедуры Sales.usp_GetSalesYTD
при указании фамилии для входного параметра и сохранения выходного значения в переменной @SalesYTD
.
USE AdventureWorks2022;
GO
CREATE PROCEDURE Sales.usp_GetSalesYTD
@SalesPerson NVARCHAR(50) = NULL,
@SalesYTD MONEY=NULL OUTPUT
AS
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify a last name for the sales person.'
RETURN (1)
END
ELSE
BEGIN
IF(SELECT COUNT(*)FROM HumanResources.vEmployee WHERE LastName=@SalesPerson)=0
RETURN (2)
END
SELECT @SalesYTD=SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID=sp.BusinessEntityID
WHERE LastName=@SalesPerson;
IF @@ERROR<>0
BEGIN
RETURN (3)
END
ELSE
BEGIN
IF @SalesYTD IS NULL
RETURN (4)
ELSE
RETURN (0)
END
EXEC Sales.usp_GetSalesYTD;
GO
DECLARE @SalesYTDForSalesPerson money, @ret_code int;
EXECUTE Sales.usp_GetSalesYTD N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
PRINT N'Year-to-date sales for this employee is ' +
CONVERT(varchar(10), @SalesYTDForSalesPerson);
GO
Следующий пример создает программу обработки кодов возврата, которые возвращаются процедурой usp_GetSalesYTD
.
Пример.
- Объявляет переменные
@SalesYTDForSalesPerson
и@ret_code
для получения выходного значения и кода возврата процедуры. - Выполняет процедуру
Sales.usp_GetSalesYTD
с входным значением, указанным для параметра@SalesPerson
, и сохраняет выходное значение и код возврата в переменных. - Проверяет код возврата в
@ret_code
и вызывает метод PRINT (Transact-SQL) для отображения соответствующего сообщения.
DECLARE @SalesYTDForSalesPerson money, @ret_code int;
EXECUTE @ret_code = Sales.usp_GetSalesYTD
N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
IF @ret_code = 0
BEGIN
PRINT 'Procedure executed successfully';
PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson);
END
ELSE IF @ret_code = 1
PRINT 'ERROR: You must specify a last name for the sales person.';
ELSE IF @ret_code = 2
PRINT 'ERROR: You must enter a valid last name for the sales person.';
ELSE IF @ret_code = 3
PRINT 'ERROR: An error occurred getting sales value.';
ELSE IF @ret_code = 4
PRINT 'ERROR: No sales recorded for this employee.';
GO
Связанный контент
Дополнительные сведения о хранимых процедурах и связанных понятиях см. в следующих статьях: