November 11, 2011 at 4:27 pm
I'm stumped, and I need help solving a mystery regarding assingnment of a variable to the output parameter of a stored procedure. The output parameter is defined as "@status int = -1 output", and the procedure assigns it different incremental values after completing various steps in the process. The purpose of returning @status is to determine at what step the procedure completed, or if the procedure failed with an error, it will provide a clue about which step failed.
The problem is that for some specific types of errors, the variable assigned to the output parameter will come back with a value of NULL. To be more specific, the variable will retain it's orginal value before it was passed to the procedure. However, I know the parameter assigned to the variable is in fact being populated with a value, because in my test procedures below, it's being returned from a SELECT statement.
To set this up, I'm presenting two versions of a procedure; one aborts with an "Invalid object name" and one aborts with "Divide by zero error". Both of these procedures compile, both execute, and both return a value of 1 from the select statement.
However, only the 2nd procedure will assign a value of 1 to the variable @p1. Why?
create proc test1
(
@status int = -1 output
)
as
select @status = 1;
select @status '@status';
select count(*) from ThisTableDoesNotExist;
go
create proc test2
(
@status int = -1 output
)
as
select @status = 1;
select @status '@status';
select (2 / 0);
go
declare @p1 int;
exec test1 @status = @p1 output;
select @p1 '@p1';
@status
-----------
1
Msg 208, Level 16, State 1, Procedure test1, Line 8
Invalid object name 'ThisTableDoesNotExist'.
@p1
-----------
NULL
declare @p1 int;
exec test2 @status = @p1 output;
select @p1 '@p1';
@status
-----------
1
Msg 8134, Level 16, State 1, Procedure test2, Line 9
Divide by zero error encountered.
@p1
-----------
1
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 12, 2011 at 1:29 pm
I'm probably not using the correct terminology, but let's give it a shot. To summarize, procedure Test1 contains a compile-time error (unknown table), while procedure Test2 contains a runtime-error (division by zero). SQL Server allows you to create a stored procedure which references not yet created tables (deferred name resolution). When the procedure is executed for the first time, a query plan for each statement in the procedure will be generated and executed, but when it encounters a statement referencing a non-existing table, it will generate an error and the procedure is aborted immediately. From BOL:
In the resolution stage, Microsoft SQL Server 2005 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.
Procedure Test2 will pass the resolution stage and will be executed, although with a division by zero error.
Peter
November 12, 2011 at 2:04 pm
Peter Brinkhaus (11/12/2011)
I'm probably not using the correct terminology, but let's give it a shot. To summarize, procedure Test1 contains a compile-time error (unknown table), while procedure Test2 contains a runtime-error (division by zero).
Exactly. It's an error at the binding stage (before optimisation and execution)
Edit: clarified
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2011 at 2:24 pm
GilaMonster (11/12/2011)
Peter Brinkhaus (11/12/2011)
I'm probably not using the correct terminology, but let's give it a shot. To summarize, procedure Test1 contains a compile-time error (unknown table), while procedure Test2 contains a runtime-error (division by zero).Exactly. Nothing in procedure 1 executes because it fails at the binding stage (before optimisation and execution)
Thanks for the confirmation, Gail. Actually I was a little hesitant to state that nothing executes, because
select @status = 1;
select @status '@status';
gives an output and using the profiler you can see these statements are actually executed. However, again using the profiler, you can see that the statement referencing the unknown table fails at compile-time and the assignment to @status got lost due to the termination of the procedure.
Peter
November 12, 2011 at 3:51 pm
There's all sorts of deferred name resolution and late-binding and recompiles going on so, yes, the first will actually execute, but the bottom line is that the first is a parse/binding error and the second is an execution error, and the two are handled very differently (you can't try-catch a parse/bind error)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2011 at 5:58 pm
GilaMonster (11/12/2011)
There's all sorts of deferred name resolution and late-binding and recompiles going on so, yes, the first will actually execute, but the bottom line is that the first is a parse/binding error and the second is an execution error, and the two are handled very differently (you can't try-catch a parse/bind error)
Thanks, guys. My two sample procedures are perhaps contrived and an oversimplification of my problem. The actual stored procedure giving my isses is an 8,000 line ETL process, with mutiple steps that leverage linked server connections to EXEC() AT remote queries on Oracle servers, tranform the result, and then insert the result into tables in a SQL Server datamart. It's actually not local tables in the SQL Server datamart that occasionally get renamed or dropped (I own that and run a tight ship), but rather occasional reorganization or unavailability on the Oracle side. Causes for failure of the stored procedure range from timeouts (it can run anywhere from seconds to over an hour depending), loss of network connectivity, or occasional unavailability of the Oracle instance (it's a replication of their production server).
If the procedure actually executes, and the output parameter (@status) is assigned a value, then I was expecting the referencing variable (@p1) to receive that value, even if the procedure fails at a subsequent point. I see that's not the case for binding errors.
However, my real question is: Under what scenarios, what specific types (or severity code) of errors, would result in the value of the output parameter not being assigned to the variable passed to it by reference; I mean is this doumented somewhere in detail?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 13, 2011 at 4:33 am
It's not about severities (though anything above 19 will do that). It's about where the error is thrown from. Any form of parse/bind error (object does not exist, column does not exist) will likely do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply