Retornar dados de um procedimento armazenado

2025-01-23

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

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.

Os exemplos de código Transact-SQL neste artigo usam o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que você pode baixar na home page 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 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 conjuntos de resultados pequenos, os resultados são armazenados temporariamente para retornar 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, procedimentos armazenados aninhados e lotes Transact-SQL de nível superior.

Exemplos de dados de retorno usando um conjunto de resultados

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
     INNER 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 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 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 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. Salva 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

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 executada passaria o valor da 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. Isso geralmente é chamado de capacidade 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 do cursor não pode ser associado 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 associados antes que um aplicativo possa executar um procedimento, os procedimentos com parâmetros de saída do cursor não podem ser chamados 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 é o mesmo que 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. Esse também é o caso se um cursor é atribuído a um parâmetro, mas esse cursor nunca é 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 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. Especifique o código de retorno para um procedimento usando 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 AS 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 do parâmetro necessário não é especificado.
`2`	O valor do 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 usar o nome da família de uma vendedora.
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 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 do ano a ano para o vendedor com o nome de família especificado e atribui-o 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 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 nome de família 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
     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 controlar os códigos de retorno retornados do procedimento usp_GetSalesYTD .