Error handling in nested stored procedures

Last Post 17 Apr 2006 11:44 PM by Taryck. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Craig HB
New Member
New Member

--
14 Feb 2006 08:14 AM
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
SQLUSA
New Member
New Member

--
17 Feb 2006 05:21 AM
The checking you do after exec StoredProcName statement is suspect.

Check BOL on that.

Kalman Toth, Database Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World
natasha
New Member
New Member

--
17 Feb 2006 06:41 AM
Craig,
All you have to do is move your Begin transaction:
before -
if (@@trancount=0) begin transaction
exec @retvalue = sp2

After:
if (@@trancount=0)
exec @retvalue = sp2
begin transaction


And it should work fine.
Nat
Taryck
New Member
New Member

--
17 Apr 2006 11:44 PM
Hi,

You could find an explanation in this article :
http://www.vfpconversion.com/Articl...id=0305111

I prefer point of view N°2 of this articles that put multi level transaction.
I add my own handling system with using Mix of point of view N°1 (allowing using transaction) save point (SAVE TRAN @SavePoint)
Where @SavePoint is char(36) GUID.

So with such solution you could ensure (what ever upper call makes) that your SP have made a rollback using ROLLBACK TRAN @SavePoint

Remember that with this solution local rollback has been ensure but the overall rollback depend only of callers proper error handling.

Taryck the Kind muslem

Here is a template that, I hope will help you :
CREATE PROCEDURE My_SP
@Rank varchar(50) = ''
,@LocalTran bit = 1 -- Define Using Local Transactions

AS
/*===== Error Handling =====*/
DECLARE @RetVal int, @Error int, @Rowcount int
DECLARE @MSG varchar(400), @SQL varchar(400)
/* Remember that some opperation couldn't be made in Transaction such as DB save or Restore */
DECLARE @Trans bit, @SavePoint GUID

/* Transaction is required for SAVE TRANS */
IF @LocalTran = 1 OR @@TRANCOUNT = 0
BEGIN
SET @Trans = 1
/* using transaction allow you to do not overwrite caller save points (if same simple name is used) */
BEGIN TRANSACTION
END
SET @Savepoint = newid()
SAVE TRAN @Savepoint -- Save point for local overall rollback
/*===== Error Handling =====*/

/* Show progress message */
RAISERROR ('%s - Synch_CPT', 0,1,@Rank)
IF @Rank <> '' SET @Rank = @Rank + '.'

---------------------------------------------------
/* Here strat the real job */
RAISERROR ('%s1 - First Step', 0,1, @Rank)
/* Save SQL code before make SQL code */
SET @SQL = 'PRINT GetDate()'
PRINT GetDate()
If @@error <> 0 goto ERR_HANDLER
---------------------------------------------------
/* And So on... */
RAISERROR ('%s2 - Next Step', 0,1, @Rank)
---------------------------------------------------

-- Normal exit
IF @Trans = 1 AND @@TRANCOUNT > 0
COMMIT
RETURN(0)

/*===== Error Handling =====*/
ERR_HANDLER:
SET @MSG = @RANK + ' - SQL query Error in Stored procedure My_SP : ' + ISNULL(@SQL,'')
RAISERROR (@MSG, 10,1)
ROLLBACK TRAN @Savepoint -- Rollback local work
IF @Trans = 1
BEGIN
IF @@TRANCOUNT > 1
COMMIT
ELSE
IF @@TRANCOUNT = 1
ROLLBACK -- Rollback only at last level
END
RETURN (-1)
/*===== Error Handling =====*/
You are not authorized to post a reply.

Acceptable Use Policy