Error handling in nested stored procedures

I have a problem with my error handling in nested stored procedures (SQL
Server 2000).
Below is an example of stored procedures that are causing this problem. If
you install the scripts and run sp1, you'll get 2 errors:
(1) Divide by zero error encountered. -- This is expected due to : select 1/
0
(2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
This 2nd error is the problem and I'm not sure what is it.
If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')",
where Col1 is an int, I get : Syntax error converting the varchar value
'hello' to a column of data type int. BUT I don't get that 2nd error about
the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
error, my error handling works perfectly.
Does anyone know what is causing this problem, and how I should change my
error handling to avoid it.
Thanks,
Craig
CREATE proc sp2
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
select 1/0
select @.err=@.@.error if (@.err<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SP
GO
CREATE proc sp1
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
exec @.retvalue = sp2
select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount<>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SPIn SQL Server, you get this error if you, for instance, begin a transaction
in one procedure and end
it in another procedure. In short, if you exist a procedure with a different
@.@.TRANCOUNT compared to
when you entered the procedure, you get this error. The reason why it seems
to work when you assign
'hello' to an int is that SQL Server will stop execution in such case. In sh
ort, have the
transaction handling in the outermost procedure. Or, you can in the inner pr
ocedure check
@.@.TRANCOUNT when you enter and SAVE TRAN if it was > 0 and in such case do t
he rollback to the
savepoint, but you still have to communicate to the caller that you didn't p
erform the work so the
caller can ROLLBACK its work. Check out the error handling articles at www.sommark
og.se for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:8F9E1557-B00E-48BB-AA9F-1C7436C1B547@.microsoft.com...
>I have a problem with my error handling in nested stored procedures (SQL
> Server 2000).
> Below is an example of stored procedures that are causing this problem. If
> you install the scripts and run sp1, you'll get 2 errors:
> (1) Divide by zero error encountered. -- This is expected due to : select
1/0
> (2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> This 2nd error is the problem and I'm not sure what is it.
> If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')"
,
> where Col1 is an int, I get : Syntax error converting the varchar value
> 'hello' to a column of data type int. BUT I don't get that 2nd error about
> the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
> error, my error handling works perfectly.
> Does anyone know what is causing this problem, and how I should change my
> error handling to avoid it.
> Thanks,
> Craig
>
> CREATE proc sp2
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> select 1/0
> select @.err=@.@.error if (@.err<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
> GO
> CREATE proc sp1
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> exec @.retvalue = sp2
> select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount<>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
>