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

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics AnalyticsPlatform 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 при выполнении процедуры:

• Для курсора последовательного доступа в результирующий набор курсора будут возвращены только строки с текущей позиции курсора до конца курсора. Текущая позиция курсора определяется при окончании выполнения процедуры. Например:

  • Курсор без прокрутки открывается в процедуре в результирующем наборе с именем 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//CATCHTHROW обработки ошибок в коды возврата Transact-SQL иногда требуется определить успешность или сбой хранимых процедур. Хранимые процедуры всегда должны указывать на сбой с ошибкой (созданной при THROW/RAISERROR необходимости) и не полагаться на код возврата, чтобы указать на сбой. Кроме того, следует избегать использования кода возврата для возврата данных приложения.

Примеры кодов возврата

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

Этот пример создает процедуру с именем 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

Связанный контент

Дополнительные сведения о хранимых процедурах и связанных понятиях см. в следующих статьях:

• CREATE PROCEDURE (Transact-SQL)
• PRINT (Transact-SQL)
• SET @local_variable (Transact-SQL)
• RETURN (Transact-SQL)
• @@ERROR (Transact-SQL)