SQL Server – Output Parameter Not Set if Stored Procedure Fails Inside TRY/CATCH

error handlingparametersql servert-sql

In SQL Server 2008 (but also in 2014). Let's consider a procedure that has an output parameter. This procedure may produce an error (and will in the following example). I note that the behaviour of the output parameter is not the same if we call the procedure within a TRY / CATCH block.

Example:

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

If we launch the procedure the simple way:

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

we get (and I'm fine with it):

Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
This is an error
Result is: error

If the procedure is now in a try/catch block:

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

we get (and I'm upset):

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

The error message is OK, but the output parameter is now NULL. If, in a TRY...CATCH context, the execution is halted immediately after the RAISERROR, I would have expected the output value to be set to hello.

Why is it so?

Best Answer

You're off to a good start with that test setup, but it is missing something that is causing you to misinterpret what is actually happening. If you put in PRINT statements at the beginning, middle, and end of the stored procedure then the additional output will make it clearer as to what is going on here. For example:

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

The output from your first test query is:

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

And that is probably what you were expecting anyway. But, the output from the second test query is:

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

That is quite a bit different. We can now see that within the TRY...CATCH construct, execution is halted immediately upon the RAISERROR being called (i.e. it becomes a batch-aborting event). On the other hand, RAISERROR does not immediately halt execution when not called within a TRY...CATCH construct.

However, as you pointed out in your update to the question, this does not explain why the OUTPUT parameter is not then set to hello. That is due to the intention of normal stored procedure behavior to not reflect partial execution (due to a batch-aborting error). This is discussed in the following blog post:

TSQL Basics II – Parameter Passing Semantics

Meaning: even though the stored procedure did execute the step setting the variable to hello, the RAISERROR is now a batch-aborting error when called within a TRY...CATCH construct, and stored procedures do not reflect any changes to parameters when they are aborted.

This behavior is also at the heart of the following explanation:

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

Related Question