Retornar dados de um procedimento armazenado

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Há três formas de retornar dados de um procedimento para um programa de chamada: conjuntos de resultados, parâmetros de saída e códigos de retorno. Este artigo fornece informações sobre as três abordagens.

Retornar dados usando conjuntos de resultados

Se você incluir uma instrução SELECT no corpo de um procedimento armazenado (mas não SELECT... INTO ou INSERT... SELECT), as linhas especificadas pela instrução SELECT serão enviadas diretamente ao cliente. Para conjuntos de resultados grandes, a execução do procedimento armazenado não continuará para a próxima instrução até que o conjunto de resultados tenha sido completamente enviado ao cliente. Para pequenos conjuntos de resultados, os resultados são colocados em spool para retornar ao cliente e a execução continuará. Se várias dessas instruções SELECT forem executadas durante a execução do procedimento armazenado, vários conjuntos de resultados serão enviados ao cliente. Esse comportamento também se aplica a lotes Transact-SQL aninhados, procedimentos armazenados aninhados e lotes Transact-SQL de nível superior.

Exemplos de dados de retorno usando um conjunto de resultados

O exemplo a seguir usa o AdventureWorks2022banco de dados de amostra. Esse exemplo mostra um procedimento armazenado que retorna os valores LastName e SalesYTD para todas as linhas SalesPerson que também aparecem na exibição 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 

Retornar dados usando um parâmetro de saída

Caso a palavra-chave de saída seja especificada para um parâmetro na definição do procedimento, o procedimento poderá retornar o valor atual do parâmetro para o programa de chamada na saída do procedimento. Para salvar o valor do parâmetro em uma variável que poderá ser usada no programa de chamada, o programa de chamada precisará usar a palavra-chave de saída ao executar o procedimento. Para obter mais informações quais tipos de dados podem ser usados como parâmetros de saída, veja CREATE PROCEDURE (Transact-SQL).

Exemplos de parâmetros de saída

O exemplo a seguir mostra um procedimento com parâmetros de entrada e de saída. O parâmetro @SalesPerson receberia um valor de entrada especificado pelo programa de chamada. A instrução SELECT usa o valor passado para o parâmetro de entrada para obter o valor SalesYTD correto. A instrução SELECT também atribui o valor ao parâmetro de saída @SalesYTD , que retorna o valor ao programa de chamada quando o procedimento sai.

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 

O exemplo a seguir chama o procedimento criado no primeiro exemplo e salva o parâmetro de saída @SalesYTD retornado do procedimento chamado na variável @SalesYTDBySalesPerson.

O exemplo:

• Declara a variável @SalesYTDBySalesPerson para receber o valor de saída do procedimento.
• Executa o procedimento Sales.uspGetEmployeeSalesYTD especificando um sobrenome para o parâmetro de entrada. Salva o valor de saída na variável @SalesYTDBySalesPerson.
• Chama PRINT para exibir o valor salvo em @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

Valores de entrada também podem ser especificados para parâmetros de saída quando o procedimento é executado. Isso permite que o procedimento receba um valor do programa de chamada, altere-o ou realize operações com o valor e, em seguida, retorne o novo valor para o programa de chamada. No exemplo anterior, um valor pode ser atribuído à variável @SalesYTDBySalesPerson antes de o programa chamar o procedimento Sales.uspGetEmployeeSalesYTD . A instrução execute passaria o valor de variável @SalesYTDBySalesPerson para o parâmetro de saída @SalesYTD. Depois, no corpo do procedimento, o valor poderia ser usado em cálculos que geram um novo valor. O novo valor seria devolvido do procedimento pelo parâmetro de saída, atualizando o valor na variável @SalesYTDBySalesPerson, quando o procedimento saísse. Isto é denominado frequentemente como "capacidade de passagem-por-referência".

Se você especificar a saída para um parâmetro ao chamar um procedimento e esse parâmetro não estiver definido usando a saída na definição do procedimento, você obterá uma mensagem de erro. Entretanto, é possível executar um procedimento com parâmetros de saída e não especificar a saída ao executar o procedimento. Nenhum erro é retornado, mas você não pode usar o valor de saída no programa de chamada.

Usar o tipo de dados de cursor em parâmetros de saída

Os procedimentos Transact-SQL podem usar o tipo de dados de cursor apenas para parâmetros de saída. Se o tipo de dados de cursor for especificado para um parâmetro, as palavras-chaves de variante e saída deverão ser especificadas para esse parâmetro na definição do procedimento. Um parâmetro pode ser especificado como apenas de saída, mas, se a palavra-chave de variante for especificada na declaração do parâmetro, o tipo de dados deverá ser cursor e a palavra-chave de saída também deverá ser especificada.

Observação

O tipo de dados cursor não pode ser associado a variáveis de aplicativos por meio de APIs de banco de dados, como OLE DB, ODBC, ADO e DB-Library. Como os parâmetros de saída devem ser associados antes de um aplicativo executar um procedimento, os procedimentos com parâmetros de saída de cursor não podem ser chamados a partir das APIs do banco de dados. Esses procedimentos podem ser chamados de lotes, procedimentos ou gatilhos do Transact-SQL apenas quando a variável de saída de cursor é atribuída a uma variável de cursor local do Transact-SQL.

Regras para parâmetros de saída de cursor

As regras seguintes pertencem aos parâmetros de saída de cursor quando o procedimento é executado:

• Para um cursor de somente avanço, as linhas retornadas no conjunto de resultados do cursor são apenas as que estão na posição do cursor na conclusão da execução do procedimento, por exemplo:

  • Um cursor não rolável é aberto em um procedimento em um conjunto de resultados chamado RS de 100 linhas.

  • O procedimento busca as primeiras cinco linhas de conjunto de resultados RS.

  • O procedimento retorna ao chamador.

  • O conjunto de resultados RS retornado ao chamador consiste de linhas de seis a 100 do RS, e o cursor no chamador está posicionado antes da primeira linha do RS.

• No caso de um cursor de somente avanço, se o cursor estiver posicionado antes da primeira linha quando o procedimento sair, o conjunto de resultados inteiro será retornado ao lote, procedimento ou gatilho de chamada. No retorno, a posição do cursor é estabelecida antes da primeira linha.

• No caso de um cursor de somente avanço, se o cursor estiver posicionado depois do fim da última linha quando o procedimento sair, um conjunto de resultados vazio será retornado ao lote, procedimento ou gatilho de chamada.

  Observação

  Um conjunto de resultados vazio não é igual a um valor nulo.

• No caso de um cursor rolável, todas as linhas no conjunto de resultados são retornadas ao lote, procedimento ou gatilho de chamada quando o procedimento sai. No retorno, a posição de cursor é mantida na posição da última busca executada no procedimento.

• Para qualquer tipo de cursor, se o cursor for fechado, um valor nulo será retornado ao lote, procedimento ou gatilho de chamada. Isso também acontecerá se o cursor for atribuído a um parâmetro, mas nunca for aberto.

  Observação

  O estado fechado só tem importância no momento do retorno. Por exemplo, é válido fechar um cursor durante o procedimento, reabrindo-o no procedimento posteriormente, e retornar o conjunto de resultados desse cursor para o lote, procedimento ou gatilho de chamada.

Exemplos de parâmetros de saída de cursor

No exemplo a seguir, é criado um procedimento que especifica um parâmetro de saída, @CurrencyCursor, usando o tipo de dados cursor. O procedimento é chamado em um lote.

Primeiro, crie o procedimento que declara e abre um cursor na tabela 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  

Em seguida, execute um lote que declare uma variável de cursor local, execute o procedimento para atribuir o cursor à variável local e depois busque as linhas do cursor.

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    

Retornar dados usando um código de retorno

Um procedimento pode retornar um valor inteiro chamado código de retorno para indicar o status de execução de um procedimento. Especifique o código de retorno de um procedimento usando a instrução RETURN. Assim como em parâmetros de saída, você deve salvar o código de retorno em uma variável quando o procedimento é executado para usar o valor de código de retorno no programa de chamada. Por exemplo, a variável de atribuição @result do tipo de dados int é usada para armazenar o código de retorno do procedimento my_proc, como:

DECLARE @result int;

EXECUTE @result = my_proc;
GO

Os códigos de retorno são geralmente usados em blocos de controle de fluxo em procedimentos para definir o valor de código de retorno para cada situação de erro possível. Você pode usar a função @@ERROR após uma instrução Transact-SQL para detectar se ocorreu um erro durante a execução da instrução. Antes da introdução do tratamento de erro TRY/CATCH/THROW no Transact-SQL, era necessário que, às vezes, os códigos de retorno determinassem o êxito ou a falha dos procedimentos armazenados. Os procedimentos armazenados devem sempre indicar falha com um erro (gerado com THROW/RAISERROR se necessário) e não contar com um código de retorno para indicar a falha. Além disso, você também deve evitar o uso de código de retorno para retornar dados de aplicativo.

Exemplos de códigos de retorno

O exemplo a seguir mostra o procedimento usp_GetSalesYTD com tratamento de erros que define valores de código de retorno especiais para vários erros. A tabela a seguir mostra o valor de inteiro atribuído pelo procedimento a cada erro possível, e o significado correspondente de cada valor.

Valor do código de retorno	Significado
0	Execução bem-sucedida
1	O valor de parâmetro necessário não foi especificado.
2	O valor de parâmetro especificado não é válido.
3	Erro ocorrido ao obter o valor de vendas.
4	Valor de vendas NULL encontrado para o vendedor.

O exemplo cria um procedimento chamado Sales.usp_GetSalesYTD, que:

• Declara o parâmetro @SalesPerson e define seu valor padrão como NULL. Esse parâmetro destina-se a assumir o sobrenome de um vendedor.
• Valida o parâmetro @SalesPerson.
  • Se @SalesPerson for NULL, o procedimento imprimirá uma mensagem e retornará o código de retorno 1.
  • Caso contrário, se o parâmetro @SalesPerson não for NULL, o procedimento verificará a contagem de linhas na tabela HumanResources.vEmployee com um sobrenome igual ao valor de @SalesPerson. Se a contagem for zero, o procedimento retornará o código de retorno 2.
• Consulta as vendas acumuladas do ano para o vendedor com o sobrenome especificado e as atribui ao parâmetro de saída @SalesYTD.
• Verifica se há erros de SQL Server testando @@ERROR (Transact-SQL).
  • Se @@ERROR não for igual a zero, o procedimento retornará o código de retorno 3.
  • Se @@ERROR for igual a zero, o procedimento verificará se o valor do parâmetro @SalesYTD é NULL. Se nenhuma venda acumulada no ano for encontrada, o procedimento retornará o código de retorno 4.
  • Se nenhuma das condições anteriores for verdadeira, o procedimento retornará o código de retorno 0.
• Se for atingido, a instrução final no procedimento armazenado invocará o procedimento armazenado recursivamente sem especificar um valor de entrada.

No final do exemplo, o código é fornecido para executar o procedimento Sales.usp_GetSalesYTD ao especificar um sobrenome para o parâmetro de entrada e salvar o valor de saída na variável @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

O exemplo a seguir cria um programa para controlar os códigos de retorno retornados do procedimento usp_GetSalesYTD .

O exemplo:

• Declara variáveis @SalesYTDForSalesPerson e @ret_code para recebe o valor de saída e o código de retorno do procedimento.
• Executa o procedimento Sales.usp_GetSalesYTD com um valor de entrada especificado para @SalesPerson e salva o valor de saída e o código de retorno em variáveis.
• Verifica o código de retorno em @ret_code e chama PRINT (Transact-SQL) para exibir uma mensagem apropriada.

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

Conteúdo relacionado

Para obter mais informações sobre procedimentos armazenados e conceitos relacionados, confira os seguintes artigos:

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