Retornar dados de um procedimento armazenado

2025-03-05

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada SQL do Azure Banco de Dados SQL do Azure Synapse Analytics no Microsoft Fabric

Há três maneiras 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.

Os exemplos de código neste artigo utilizam as bases de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que podem ser descarregadas a partir da página inicial do Microsoft SQL Server Samples and Community Projects.

Retornar dados usando conjuntos de resultados

Se você incluir uma instrução SELECT no corpo de um procedimento armazenado (mas não um SELECT ... INTO ou INSERT ... SELECT), as linhas especificadas pela instrução SELECT serão enviadas diretamente ao cliente. Para grandes conjuntos de resultados, 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 armazenados temporariamente para devolução ao cliente, e a execução continua. Se várias 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, a procedimentos armazenados aninhados e a lotes Transact-SQL de nível superior.

Exemplos de retorno de dados usando um conjunto de resultados

Este exemplo mostra um procedimento armazenado que retorna os valores LastName e SalesYTD para todas as linhas SalesPerson que também aparecem no modo de 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
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

Retornar dados usando um parâmetro de saída

Se você especificar a palavra-chave output para um parâmetro na definição do procedimento, o procedimento poderá retornar o valor atual do parâmetro para o programa chamador quando o procedimento for encerrado. Para salvar o valor do parâmetro em uma variável que pode ser usada no programa de chamada, o programa de chamada deve usar a palavra-chave output ao executar o procedimento. Para obter mais informações sobre quais tipos de dados podem ser usados como parâmetros de saída, consulte CREATE PROCEDURE.

Exemplos de parâmetros de saída

O exemplo a seguir mostra um procedimento com um parâmetro de entrada e um parâmetro de saída. O parâmetro @SalesPerson receberia um valor de entrada especificado pelo programa chamador. 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 para o programa de chamada quando o procedimento é encerrado.

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
     INNER 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 nome de família para o parâmetro de entrada. Salve o valor de saída na variável @SalesYTDBySalesPerson.
Chama PRINT para exibir o valor salvo em @SalesYTDBySalesPerson.

DECLARE @SalesYTDBySalesPerson AS MONEY;

EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe',
    @SalesYTD = @SalesYTDBySalesPerson OUTPUT;

PRINT 'Year-to-date sales for this employee is '
    + CONVERT (VARCHAR (10), @SalesYTDBySalesPerson);
GO

Os 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 ou execute operações com o valor e, em seguida, retorne o novo valor para o programa de chamada. No exemplo anterior, a variável @SalesYTDBySalesPerson pode receber um valor antes que o programa chame o procedimento Sales.uspGetEmployeeSalesYTD. A instrução executada passaria o valor da variável @SalesYTDBySalesPerson para o parâmetro de saída @SalesYTD. Em seguida, no corpo do procedimento, o valor poderia ser usado para cálculos que geram um novo valor. O novo valor seria passado de volta para fora do procedimento através do parâmetro de saída, atualizando o valor na variável @SalesYTDBySalesPerson quando o procedimento for encerrado. Isto é muitas vezes referido como capacidade de de passagem por referência.

Se você especificar a saída para um parâmetro quando chamar um procedimento e esse parâmetro não for definido usando a saída na definição do procedimento, você receberá uma mensagem de erro. No entanto, você pode 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 do cursor nos parâmetros de saída

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

Observação

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

Regras para parâmetros de saída do cursor

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

Para um cursor somente para frente, as linhas retornadas no conjunto de resultados do cursor são apenas aquelas linhas na posição do cursor e além dela, na conclusão da execução do procedimento. Por exemplo:
- Um cursor não rolável é aberto num procedimento em um conjunto de resultados chamado RS de 100 linhas.
- O procedimento busca as cinco primeiras linhas do conjunto de resultados RS.
- O procedimento retorna ao chamador.
- O conjunto de resultados RS devolvido ao chamador consiste em linhas de 6 a 100 de RS, e o cursor no chamador é posicionado antes da primeira linha de RS.
Para um cursor somente para frente, se o cursor estiver posicionado antes da primeira linha quando o procedimento for encerrado, todo o conjunto de resultados será retornado para o lote, procedimento ou gatilho de chamada. Quando retornada, a posição do cursor é definida antes da primeira linha.
Para um cursor somente para frente, se o cursor estiver posicionado além do final da última linha quando o procedimento for encerrado, um conjunto de resultados vazio será retornado para o lote, procedimento ou gatilho de chamada.

Observação

Um conjunto de resultados vazio não é o mesmo que um valor nulo.
Para um cursor rolável, todas as linhas no conjunto de resultados são retornadas para o lote de chamada, procedimento ou gatilho quando o procedimento é encerrado. Quando retornada, a posição do cursor é deixada na posição da última busca executada no procedimento.
Para qualquer tipo de cursor, se o cursor estiver fechado, um valor nulo será passado de volta para o lote, procedimento ou gatilho de chamada. Este também é o caso se um cursor é atribuído a um parâmetro, mas esse cursor nunca é aberto.

Observação

O estado fechado só importa na hora do retorno. Por exemplo, é válido fechar um cursor no meio de um procedimento, abri-lo novamente mais tarde dentro do procedimento e devolver o conjunto de resultados desse cursor para o lote, procedimento ou trigger que faz a chamada.

Exemplos de parâmetros de saída do cursor

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

Primeiro, crie o procedimento que declara e, em seguida, abra 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, em seguida, busque as linhas do cursor.

USE AdventureWorks2022;
GO

DECLARE @MyCursor AS CURSOR;

EXECUTE 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. Você especifica o código de retorno para um procedimento usando o RETURN. Assim como acontece com os 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 do 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 AS INT;
EXECUTE @result = my_proc;
GO

Os códigos de retorno são comumente usados em blocos de controle de fluxo dentro de procedimentos para definir o valor do código de retorno para cada possível situação de erro. Você pode usar a função @@ERROR após uma instrução Transact-SQL para detetar se ocorreu um erro durante a execução da instrução. Antes da introdução do tratamento de erros TRY/CATCH/THROW em Transact-SQL códigos de retorno às vezes eram necessários para determinar o sucesso 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 depender de um código de retorno para indicar a falha. Além disso, você deve evitar usar o código de retorno para retornar dados do aplicativo.

Exemplos de códigos de devolução

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 inteiro atribuído pelo procedimento a cada erro possível e o significado correspondente para cada valor.

Valor do código de retorno	Significado
`0`	Execução bem-sucedida.
`1`	O valor do parâmetro obrigatório não é especificado.
`2`	O valor do parâmetro especificado não é válido.
`3`	Ocorreu um erro ao obter o valor das vendas.
`4`	`NULL` valor de vendas 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. Este parâmetro destina-se a tomar o nome de família de um vendedor.
Valida o parâmetro @SalesPerson.
- Se @SalesPerson estiver NULL, o procedimento imprime uma mensagem e retorna o código de retorno 1.
- Caso contrário, se o parâmetro @SalesPerson não estiver NULL, o procedimento verificará a contagem de linhas na tabela HumanResources.vEmployee com um nome de família igual ao valor de @SalesPerson. Se a contagem for zero, o procedimento retornará o código de retorno 2.
Consulta as vendas desde o início do ano até à data para o vendedor com o apelido especificado e atribui-as ao parâmetro de saída @SalesYTD.
Verifica se há erros do SQL Server testando @@ERROR.
- Se @@ERROR não for igual a zero, o procedimento retornará o código de retorno 3.
- Se @@ERROR foi igual a zero, o procedimento verifica se o valor do parâmetro @SalesYTD é NULL. Se não forem encontradas vendas relativas ao ano corrente, o procedimento devolve o código de devolução 4.
- Se nenhuma das condições anteriores for verdadeira, o procedimento retornará o código de retorno 0.
Se alcançado, a instrução final no procedimento armazenado invoca 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 enquanto especifica um nome de família para o parâmetro de entrada e salva 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
     INNER 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
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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 manipular os códigos de retorno retornados do procedimento usp_GetSalesYTD.