trapping errors in cascading sprocs

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trapping errors in cascading sprocs

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2004-07-08 : 14:40:34

I have a sproc that calls another sproc. Both have error checking. If an error is thrown in the child sproc, the rollbacks occur as expected but an extra error message is returned saying I'm missing some COMMIT Statement. If I throw an error for each sproc individually everything works fine. How can I not have the "extra" message returned? I've removed most of the logic from the sproc to try to make things clearer (the top sproc calls the bottom sproc).

Am I missing some commit statement? I don't see the error

This is what is returned


1st error: 	The zip code provided is invalid 

extra error:	Transaction count after EXECUTE indicates that 
                a COMMIT or ROLLBACK TRANSACTION statement is 
                missing. Previous count = 1, current count = 0. 
                The ROLLBACK TRANSACTION request has no 
                corresponding BEGIN TRANSACTION. 

2nd error: 	Error updating applicant 



CREATE PROCEDURE UpdateHomeQuoteApplicant
(
@QuoteID int
,@Zip varchar(20) 
)
AS
DECLARE 
@errno int
,@errmsg varchar(200)

SET @errno = 50005
SET @errmsg = 'Error updating applicant'

--begin transaction
SET NOCOUNT ON
BEGIN TRAN		

IF (1=1)
    BEGIN
	EXEC SetHomeQuoteTerritories @QuoteID, @Zip
	-- check for error
	IF (@@ERROR <> 0)  GOTO errorblock
    END
	

-- Success. Commit the transaction.   
COMMIT TRAN
SET NOCOUNT OFF
RETURN(0)

errorblock:
	-- An error occurred. Rollback the transaction.
	ROLLBACK TRAN
	SET NOCOUNT OFF
	RAISERROR @errno @errmsg
GO



CREATE PROCEDURE SetHomeQuoteTerritories
(
@QuoteID int
,@Zip varchar(20)
)
AS
DECLARE
@errno int
,@errmsg varchar(200)

SET @errno = 50005
SET @errmsg = 'Error setting territory data'

--begin transaction
SET NOCOUNT ON
BEGIN TRAN

--first check to see if this is a valid zipcode
IF (1=1) 
    BEGIN
	Set @errmsg = 'The zip code provided is invalid'
	GOTO errorblock
    END

-- do more stuf

-- Success. Commit the transaction.   
COMMIT TRAN
SET NOCOUNT OFF
RETURN(0)

errorblock:
	-- An error occurred. Rollback the transaction.
	ROLLBACK TRAN
	SET NOCOUNT OFF
	RAISERROR @errno @errmsg
GO

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 18:26:36

Change second SProc to:


[code]
CREATE PROCEDURE SetHomeQuoteTerritories
(
@QuoteID int
,@Zip varchar(20)
)
AS
DECLARE
@errno int
,@errmsg varchar(200)

SET @errno = 50005
SET @errmsg = 'Error setting territory data'

--begin transaction
SET NOCOUNT ON
BEGIN TRANSACTION SetHomeQuoteTerritories_01
SAVE  TRANSACTION SetHomeQuoteTerritories_02

--first check to see if this is a valid zipcode
IF (1=1) 
    BEGIN
	Set @errmsg = 'The zip code provided is invalid'
	GOTO errorblock
    END

-- do more stuf

-- Success. Commit the transaction.   
COMMIT TRANSACTION SetHomeQuoteTerritories_01
SET NOCOUNT OFF
RETURN(0)

errorblock:
	-- An error occurred. Rollback the transaction.
ROLLBACK TRANSACTION SetHomeQuoteTerritories_02
COMMIT TRANSACTION SetHomeQuoteTerritories_01
	SET NOCOUNT OFF
	RAISERROR @errno @errmsg
GO

Kristen

nic
Posting Yak Master

209 Posts

Posted - 2004-07-09 : 11:22:23

Thanks for the help. That works. I'm a little confused why though. Please let me know if my understanding is correct. Since the parent is already in a transaction, opening another transaction in the child doesn't really make a difference (since neither have unique names). When in the child sproc, calling "rollback transaction" rolls the transaction back all the way to the beginning of the parent transaction. Then when the parent tries to roll back, there is nothing to rollback so the transaction count is off and the error is raised? Is this correct? It seems that the rollback in the child would only go to the "begin" statement in the child.

Another confusing thing happened. When troubleshooting, I removed the "goto error" statement in the child and replaced it with "raiserror" (so don't roll anything back, just throw an error). When this happens the child error message is returned but the parent error message isn't. In fact the parent sproc continues to completion and commits the transaction. (???) If the child raised an error, shouldn't it be caught by the parent? (could this scenario be caused by the beginning a transaction in the child but not rolling it back?)

My main concern is I need the child sproce to work independately. Sometimes it will be called directly and sometimes it will be called from another sproc.

I have read up about transactions but a little clarification would be greatly appreciated.

Nic

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 12:30:03

You've got a good grasp of it, but I think its more simple than

Then when the parent tries to roll back, there is nothing to rollback so the transaction count is off and the error is raised?

When the ChildSProc returns, having done a ROLLBACK, SQL sees that the @@TRANCOUNT is LOWER than when the ChildSProc started, and it raises a warning message. ADO harvests that warning message and plays merry hell with the application's attempts to get at its data!

Separately if you ALSO do a ROLLACBK in the ParentSProc that will be an error, 'coz you are at @@TRANCOUNT=0 already and cannot ROLLBACk to a lower level (but that can be prevented by NOT doing a ROLLBACK if @@TRANCOUNT is ALREADY 0).

So the artifical Transaction within the ChildProc, and the SavePoint to roll back to, means that when ChildProc returns @@TRANCOUNT will always be the same as when it started.

raiserror is a different kettle of fish. I think it does nothing except set @@ERROR. You have to test for that, so you need something like:


EXEC @intErrNo = MySProc ...
IF @intErrNo <> 0 OR @@ERROR <> 0
   ... handle error ...

Using a RAISERROR won't help you (IMHO) if the ChildSProc is called direct - there is nothing "external" to do the ROLLBACK. We have exactly the same scenario as you - SProcs which are either called direct, or from another SProc. For example, "Save a customer record including all business rules processing". This is called from our "Customer Maintenance Form" and also whenever any other Sproc wants to, for example, change the Status of the customer. The Wrapper Transaction method seems to work well for us, plus we also RETURN an errorcode if the SProc fails. This will cause the outer SProc to rollback any transaction block (if it had one - it too will be using a Wrapper Transaction) and to cascade the error code up to its caller - all the way back to the application.

We also have an OUTPUT variable to be able to pass back a concatenated string message about the error(s) that have happened, and an optional "@intSilent" parameter - which basically stops an SProc outputing any "error" result sets - this is set when the SProc is called by ANOTHER SProc, but NOT when it is called by the application.

Just before RETURNing,if @intErrNo<>0 AND @intSilent=0 our SProcs return a resultset with error condition details, using a special name for Column 1. Our application has a centralised "Check Validity of Resultset" function; if that sees the special column name, for the first column, it instigates the error handler - which saves having to check for error returns everywhere.

Note also that the Error Message String OUTPUT parameter gets cascaded all the way up the food chain. We started off using a LOG table in the DB, and then, of course, discovered that that got ROLLBACK'd along with the Real Data, so we never saw the duff-stuff oin the LOGs :-(

Kristen

nic
Posting Yak Master

209 Posts

Posted - 2004-07-09 : 12:59:30

Last clarifying question and then I won't bother you anymore ;)

So from the documentation:
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1

So parentSproc = 1 trans, childSproc = 1 trans. So @@Transcount = 2.

Rollback in the child really only rolls back the child trans (not all the way to the parent as I said before) BUT rollback sets transcount = 0 so when control is pass back to the parent, everything breaks down (transcounts are off sequence).

Your rollback to savepoint/commit trans keeps the transcount correct (decrements by 1 instead of setting to 0).

I'm pretty sure I got it but just wanted to make sure.
Thanks for your help.

Nic

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 13:56:25

Yup, that's it, and experiement that you can mess around with might help:


SET NOCOUNT ON
DECLARE	@intFailAt	int	-- FailPoint to test
SELECT	@intFailAt = 2	-- Set to 1 or 2, or your own value!
-- Parent SProc
SELECT [A]=@@TRANCOUNT
BEGIN TRANSACTION 

-- Enter Child SProc
SELECT [B]=@@TRANCOUNT
BEGIN TRANSACTION SetHomeQuoteTerritories_01
SELECT [C]=@@TRANCOUNT
SAVE  TRANSACTION SetHomeQuoteTerritories_02
SELECT [D]=@@TRANCOUNT
IF @intFailAt = 2 GOTO errorblock
SELECT [E]=@@TRANCOUNT
COMMIT TRANSACTION SetHomeQuoteTerritories_01
SELECT [F]=@@TRANCOUNT
GOTO NoError

errorblock:
SELECT [G]=@@TRANCOUNT
ROLLBACK TRANSACTION SetHomeQuoteTerritories_02
SELECT [H]=@@TRANCOUNT
COMMIT TRANSACTION SetHomeQuoteTerritories_01
SELECT [I]=@@TRANCOUNT

NoError:
SELECT [J]=@@TRANCOUNT
-- Exit Child SProc

-- (Note: Technically we would ROLLBACK here if the Child returned Error)
COMMIT
SELECT [K]=@@TRANCOUNT
-- Exit Parent SProc
SET NOCOUNT OFF

Note that [C] and [D] are the same, as are [G] and [H] - i.e. the ROLLBACK does NOT change the @@TRANSCOUNT but just rolls back to the savepoint, but [I] is one less after the commit - i.e. we are back to the level where we started the ChildPRoc

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources