October 7, 2003 at 5:10 pm
We are upgrading our java drivers and the sql server behavior for nested stored procedure error handling is biting us. Does anyone have suggestions on handling/detecting errors in a nested stored procedure from the outer proc, causing the outer proc to fail in the same manner that a t-sql block would. Msft's explanation of the behavior:
Error Handling Behavior in Nested Transact-SQL Blocks
Microsoft SQL Server stops the execution of the process and rolls back the transaction if a fatal error occurs in any of the outer or inner Transact-SQL blocks.
For example, one Transact-SQL block, named T2, is nested inside another Transact-SQL block, named T1. The process ends if a fatal error occurs in the inner block of T2 or the outer block of T1, and all data update statements are rolled back. If a nonfatal error occurs and the process ends, the rest of the process continues successfully.
Error Handling Behavior in Nested Stored Procedures
The error handling behavior of Microsoft SQL Server in nested procedures is different from that of the nested Transact-SQL blocks.
For example, a stored procedure, named PROC1, is called inside another stored procedure, named PROC2. The execution process of stored procedure PROC2 does not end if a fatal error occurs in PROC1. However, the execution of a stored procedure in PROC2 will end.
If any fatal error occurs in PROC1, PROC2 continues the execution.
************
In some of our apps, we call proc1 from java, and when proc2 errors, we don't get it.
Any help?
October 8, 2003 at 7:18 am
One way is to use RETURN to return non sql critical errors or user defined errors and to trap both errors after proc call
DECLARE @Result int,@ERR int
EXECUTE @Result = proc1 param,param,param...
SET @ERR = @@ERROR
IF (@ERR > 0)
--critical error
ELSE
IF @Result > 0
--non critical / user defined error
Far away is close at hand in the images of elsewhere.
Anon.
October 8, 2003 at 10:14 am
Thank you, something I'll add to my toolset for capturing both sets of errors.
I suggested a similar approach to the application developer, catching the @@ERROR and using both return and raiserror, however, the java driver is not handling it as a sql exception. With further testing, it appears that it has to do with the driver not parsing the error if a result set is also returned - this therefore does not garnner us a sql exception for the java to catch and handle. We are escalating to the driver vendor, I'll post our findings on the chance that someone else encounters this in the future.
October 8, 2003 at 10:22 am
Can you use an OUTPUT parameter in the top proc to return either error value (@ERR or @Result in my example) and test for this.
Far away is close at hand in the images of elsewhere.
Anon.
October 8, 2003 at 11:43 am
Very possibly, I suggest they try that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply