Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
podgehb
Starting Member
18 Posts |
 Posted - 2006-02-14 : 12:11:46
|
| 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,CraigCREATE proc sp2as declare @err intdeclare @retvalue intif (@@trancount=0) begin transaction select 1/0 select @err=@@error if (@err<>0) goto errorif (@@trancount>0) commit transactionreturn 0 -- successerror: if (@@trancount>0) rollback transaction if (@err<>0) return @err -- error else if (@retvalue<>0) return @retvalue -- error returned from SPGOCREATE proc sp1as declare @err intdeclare @retvalue intif (@@trancount=0) begin transaction exec @retvalue = sp2 select @err=@@error if (@err<>0)or(@retvalue<>0) goto errorif (@@trancount>0) commit transactionreturn 0 -- successerror: if (@@trancount<>0) rollback transaction if (@err<>0) return @err -- error else if (@retvalue<>0) return @retvalue -- error returned from SP |
|
|
mallier
Starting Member
24 Posts |
|
|
|
|
|
Error handling in nested stored procedures