Início / dba / Perguntas / 185416
Accepted
irimias
Asked: 2017-09-09 05:30:46 +0800 CST

Parâmetro de saída não definido se o procedimento armazenado falhar dentro de um TRY/CATCH

  • 772

No SQL Server 2008 (mas também em 2014). Vamos considerar um procedimento que tenha um parâmetro de saída. Este procedimento pode produzir um erro (e o fará no exemplo a seguir). Observo que o comportamento do parâmetro de saída não é o mesmo se chamarmos o procedimento dentro de um bloco TRY/ .CATCH

Exemplo:

create procedure test_output @result varchar(10) output
as
begin
    set @result = 'hello'
    raiserror('This is an error', 16,1)
    set @result = 'error'
end

Se iniciarmos o procedimento da maneira simples:

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

obtemos (e estou bem com isso):

Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
Este é um erro O
resultado é: erro

Se o procedimento estiver agora em um bloco try/catch:

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
    exec test_output @result = @res2 out
end try
begin catch
    set @error_message = error_message()
    raiserror(@error_message, 16,1)
    print 'Result is: '+ isnull(@res2, 'empty')
end catch

temos (e estou chateado):

Msg 50000, Level 16, State 1, Line 28
Este é um erro O
resultado é: vazio

A mensagem de erro está OK, mas o parâmetro de saída agora é NULL . Se, em um TRY...CATCHcontexto, a execução for interrompida imediatamente após o RAISERROR, eu esperaria que o valor de saída fosse definido como hello .

Por que é tão?

sql-server t-sql

1 respostas

  1. Best Answer

    Você começou bem com essa configuração de teste, mas está faltando algo que está fazendo com que você interprete mal o que realmente está acontecendo. Se você inserir PRINTinstruções no início, no meio e no final do procedimento armazenado, a saída adicional tornará mais claro o que está acontecendo aqui. Por exemplo:

    USE [tempdb];
GO
CREATE PROCEDURE test_output
(
  @Result VARCHAR(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

    SET @Result = 'hello';

    PRINT 1;
    RAISERROR('This is an error', 16, 1);

    PRINT 2;
    SET @Result = 'error';

    PRINT 3;
END;
GO

    A saída da sua primeira consulta de teste é:

    1
Msg 50000, Level 16, State 1, Procedure test_output, Line xxxx [Batch Start Line yyyyy]
This is an error
2
3
Result is: error

    E isso é provavelmente o que você estava esperando de qualquer maneira. Mas, a saída da segunda consulta de teste é:

    1
Msg 50000, Level 16, State 1, Line xxxxx
This is an error
Result is: empty

    Isso é um pouco diferente. Agora podemos ver que dentro da TRY...CATCHconstrução, a execução é interrompida imediatamente após a RAISERRORchamada (ou seja, torna-se um evento de aborto em lote). Por outro lado, RAISERRORnão interrompe imediatamente a execução quando não é chamado dentro de uma TRY...CATCHconstrução.

    No entanto, como você apontou na atualização da pergunta, isso não explica por que o OUTPUTparâmetro não está definido como hello. Isso se deve à intenção do comportamento normal do procedimento armazenado de não refletir a execução parcial (devido a um erro de anulação em lote). Isso é discutido na seguinte postagem do blog:

    TSQL Basics II – Semântica de passagem de parâmetros

    Significado: mesmo que o procedimento armazenado tenha executado a etapa configurando a variável como hello, RAISERRORagora é um erro de cancelamento de lote quando chamado dentro de uma TRY...CATCHconstrução, e os procedimentos armazenados não refletem nenhuma alteração nos parâmetros quando são cancelados.

    Esse comportamento também está no centro da seguinte explicação:

    Por que os TVPs devem ser READONLY e por que os parâmetros de outros tipos não podem ser READONLY

    • 6

relate perguntas