Procedimento armazenado SQL com parâmetro de saída

Parâmetros de saída em procedimentos armazenados são úteis para passar um valor de volta para o T-SQL chamador, que pode usar esse valor para outras coisas.

Digamos que você tenha um procedimento armazenado que retorna um estado dado a entrada de uma cidade, com estado como parâmetro de saída:

CREATE PROCEDURE [dbo].[GetStateFromCity] (@City NVARCHAR(30), @State NCHAR(2) OUTPUT)
AS
SELECT @State = [State]
FROM [ExampleTable] 
WHERE [City] = @City
GO;

Agora você pode usar este parâmetro de saída para passar um valor em outro lugar.

Por exemplo:

DECLARE @State int
EXEC [dbo].[GetStateFromCity] @City = 'Calgary', @State OUTPUT;

-- Do something with this value
SELECT @State;

-- Do something else
EXEC [dbo].[GetInsuranceCompanyByState] @State;

Para resumir, se você quiser apenas retornar um valor para um aplicativo cliente, provavelmente não precisará de um parâmetro de saída.

No entanto, se você deseja passar valores em T-SQL entre procedimentos armazenados, eles podem ser muito úteis.

Para o que vale a pena, eu quase não uso parâmetros de saída.

Supondo que essa pergunta se refira ao SQL Server: tudo se resume ao contexto e à eficiência.

Contexto = Código do aplicativo

Ao executar o procedimento armazenado a partir do código do aplicativo, não é muito diferente em termos de quantidade de código. Ao retornar um conjunto de resultados, basta chamar ExecuteReadere, SqlDataReader.Read()em seguida, obter uma linha e, em seguida, obter as colunas do arquivo SqlDataReader. Mas se você estiver obtendo apenas um único valor, poderá usar o método de atalho ExecuteScalarque obtém uma linha (mesmo que haja mais linhas) e retorna o valor na primeira coluna (mesmo que haja mais colunas). Ao retornar OUTPUTparâmetros, você só precisa chamar ExecuteNonQuery, verificar a .Valuepropriedade de cada parâmetro e converter para o tipo apropriado.

Portanto, em termos do exemplo simples de retornar um único valor, parece "mais fácil" retornar um conjunto de resultados e chamar ExecuteScalar. MAS, retornar um conjunto de resultados, seja usando ExecuteReaderou ExecuteScalar, requer mais recursos do SQL Server e do aplicativo cliente. O SQL Server precisa configurar e gerenciar o conjunto de resultados (ou seja, requer memória e tempo), e o aplicativo precisa instanciar um SqlDataReader(sim, mesmo usando ExecuteScalar) e gerenciá-lo (ou seja, requer memória e tempo). Se você tiver a garantia de ter apenas uma única linha de conjunto de resultados, é melhor (mesmo que apenas um pouco) usando parâmetros de saída.

Contexto = T-SQL

Ao executar o procedimento armazenado do T-SQL (e precisar usar o(s) valor(es) retornado(s), é pelo menos mais conveniente usar OUTPUTparâmetros. Retornar um conjunto de resultados é utilizável, mas requer a inserção dos resultados em uma tabela -- normalmente uma tabela temporária local ou variável de tabela -- usando INSERT ... EXEC. Mas você ainda precisa selecionar a linha em variáveis ​​locais. Novamente, é mais tempo e recursos para chegar ao mesmo lugar de ter os valores nas variáveis.

Agora, quando você está retornando apenas um único valor (ou seja, a mesma situação que funciona para ), às vezesExecuteScalar você pode usar uma Função Definida pelo Usuário (UDF) escalar, que tem a capacidade de ser colocada em uma consulta, que às vezes é muito útil (mesmo se houver um impacto no desempenho do uso de UDFs escalares em consultas). No entanto, existem muitas restrições sobre o que pode ser feito em UDFs, portanto, se você precisar criar tabelas temporárias ou fazer qualquer DML ou DDL, etc., usar um procedimento armazenado é a única opção.

Embora não faça parte da pergunta "conjunto de resultados vs parâmetro OUTPUT", é bom ter em mente que você pode fazer as duas coisas! Se você tiver alguns valores discretos, bem como um conjunto de dados para retornar, o procedimento armazenado permite retornar ambos, o que em raras ocasiões é bastante útil.

Duas outras coisas que acho que valem a pena destacar:

1) Você pode passar mais de um parâmetro como OUTPUT,

2) Você não precisa chamar os parâmetros com OUTPUTse não quiser os resultados

CREATE PROCEDURE ManyOutputs @a int, @b int output, @c varchar(100) output, @d bit output
AS
BEGIN
    SET @b = @a + 11
    SET @c = 'The Value of A is ' + CAST(@a AS varchar(5)) + ', and the value of B is ' + CAST(@b AS varchar(5))
    IF (@a % 2 = 1)
        SET @d = 1
    ELSE
        SET @d = 0
END
GO

Chamando esta rotina:

DECLARE @bVal int
DECLARE @cVal varchar(100)
DECLARE @dVal bit

EXEC ManyOutputs 1, @bVal, @cVal, @dVal
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Retorna NULO, NULO, NULO

EXEC ManyOutputs 2, @bVal OUT, @cVal OUT, @dVal OUT
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Retorna 13, "O valor de A é 2 e o valor de B é 13", 0

EXEC ManyOutputs 3, @bVal, @cVal, @dVal
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Retorna 13, "O valor de A é 2 e o valor de B é 13", 0

(o mesmo que a última chamada, porque não obtivemos novos valores usando OUTPUT, portanto, manteve os valores antigos.)

EXEC ManyOutputs 5, @bVal OUT, @cVal OUT, @dVal OUT
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Retorna 16, "O valor de A é 5 e o valor de B é 16", 1