Error handling in a single Sp - SQL Server Forums

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

Transact-SQL (2000)

Error handling in a single Sp

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-31 : 03:44:11


CREATE PROCEDURE [dbo].[usp_run_daily_refresh] 
AS

DECLARE @Error_int Int,
            @Err_Msg_vchr varchar(255),
            @Parameters_vchr varchar(255),
            @SP_Name_vchr varchar(30),
            @Ref_Table_Name_vchr varchar(30),
            @Error_Notes_vchr varchar(255),
            @ParmDefinition_nvchr VARCHAR(50)
            
BEGIN TRAN


EXECUTE usp_emp_detail_u 
SELECT @Error_int=@@error
 IF  @Error_int > 0 
BEGIN
	SET @SP_Name_vchr='usp_emp_detail_u'
	SET @Ref_Table_Name_vchr='emp_detail'
	GOTO Error_handler
	ROLLBACK TRAN
END


EXECUTE usp_emp_referral_detail_u
SELECT @Error_int=@@error
 IF  @Error_int > 0 
BEGIN
	SET @SP_Name_vchr='usp_emp_referral_detail_u'
	SET @Ref_Table_Name_vchr=emp_referral_detail'
	GOTO Error_handler
	ROLLBACK TRAN
END

EXECUTE usp_emp_status_detail_u
SELECT @Error_int=@@error
 IF  @Error_int > 0 
BEGIN
	SET @SP_Name_vchr='usp_emp_status_detail_u'
	SET @Ref_Table_Name_vchr='emp_status_detail'
	GOTO Error_handler
	ROLLBACK TRAN
END


EXECUTE usp_corp_company_detail_u 
SELECT @Error_int=@@error
 IF  @Error_int > 0 
BEGIN
	SET @SP_Name_vchr='usp_corp_company_detail_u'
	SET @Ref_Table_Name_vchr='corp_company_detail'
	GOTO Error_handler
	ROLLBACK TRAN
END


EXECUTE usp_bank_info_u
SELECT @Error_int=@@error
 IF  @Error_int > 0 
BEGIN
	SET @SP_Name_vchr='usp_bank_info_u'
	SET @Ref_Table_Name_vchr='bank_info'
	GOTO Error_handler
	ROLLBACK TRAN
END

COMMIT TRAN

SET @Error_Int = @@Error
Error_handler:

If (@error_Int <> 0 )
 BEGIN
    IF (@FromDal='N')    
     BEGIN
        SET @Parameters_vchr = 'None'
        SET @Err_Msg_vchr =(SELECT Description
                             FROM master..sysmessages
                             WHERE error = @Error_int)
        EXEC usp_MrErrorHandler @ErrorNo = @Error_int,
                                                @ErrorMsg = @Err_Msg_vchr,
                                                @TableName = @Ref_Table_Name_vchr,
                                                @CallingSPName = @SP_Name_vchr,
                                                @Parameters = @Parameters_vchr,
                                                @ErrorNotes = @Error_Notes_vchr
                                                
        RETURN @Error_int
     END
    ELSE                   
     BEGIN
        RETURN @Error_int
     END
 END
ELSE
 BEGIN
    RETURN 0
 END
END

Here the usp_run_daily_refresh stored procedure calls multiple stored procedures inside.If one fails it should rollback
the full transaction.Is this the correct way to capture the error or should I use something like

SET @Error_int=EXECUTE usp_bank_info_u
IF @Error_int > 0
BEGIN
SET @SP_Name_vchr='usp_bank_info_u'
SET @Ref_Table_Name_vchr='bank_info'
GOTO Error_handler
ROLLBACK TRAN
END

for all the store procedures

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 03:59:07

http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-31 : 07:21:07

GOTO Error_handler
ROLLBACK TRAN

The goto will mean that the rollback will never be executed.
It's usually a good idea to get a return code from the sp (unless it does a raiserror to return error conditions) and to check @@error

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-31 : 09:19:37

Check out this link:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50568[/url]

The revolution won't be televised!

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 10:33:32

Emphasising what Nigel said:

"It's usually a good idea to get a return code from the sp (unless it does a raiserror to return error conditions) and to check @@error"

Lots of SProcs return a value to indicate success/failure. If an Sproc hits a run-time error, or is using SET XACT_ABORT, then @@ERROR is the only useful "catch" that you can do (I think).

Also watch out for an Sproc that uses an OUTPUT parameter to return an "error state" (I appreciate you are calling your own Sproc, so will know how it handles these things, so this is just a general comment)

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-31 : 13:19:10

So Is this the correct coding way where I set @error_int to get the error value from the stored procedure and Here after the rollback will it go to the Error_handler section.

SET @Error_int=EXECUTE usp_bank_info_u
IF @Error_int > 0
BEGIN
SET @SP_Name_vchr='usp_bank_info_u'
SET @Ref_Table_Name_vchr='bank_info'
ROLLBACK TRAN
GOTO Error_handler
END

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 13:58:44

SET @Error_int=EXECUTE usp_bank_info_u
IF @Error_int > 0 OR @@ERROR <> 0
BEGIN
SET @SP_Name_vchr='usp_bank_info_u'
SET @Ref_Table_Name_vchr='bank_info'
ROLLBACK TRAN
GOTO Error_handler
END

Personally I would do the ROLLBACK TRAN in the "Error_handler" so that it was only in one place, but that's up to you.

Also note that your code style will probably cause some crappy "Transaction level mucked about with" warning message to be transmitted back to the application - which may cause it to fail. To work around this we use some convoluted code along the lines of:


BEGIN TRANSACTION MyLabel_01
SAVE  TRANSACTION MyLabel_02

... do update type stuff here ...

IF @MyErrorCode = 0
BEGIN
	COMMIT TRANSACTION MyLabel_01
END
ELSE
BEGIN
	ROLLBACK TRANSACTION MyLabel_02
	COMMIT TRANSACTION MyLabel_01
END

(the idea being that the SProc always COMMITS, albeit having possibly rolled back part of the change). In effect, if it rolls back, it rolls back ALL the changes ... but that keeps SQL Server for issuing any bogus warning messages to the application.

Kristen

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-31 : 14:56:00

The stored procedure which iam using is application independent.This is a daily process which would update different tables and the main sp is being called from a job.So there is no interaction of the application which these Sps.In this case should I use the method mentioned above.

One more thing

Is it better to use
SET @Error_int=EXECUTE usp_bank_info_u
or
EXECUTE usp_bank_info_u
SELECT @Error_int=@@error

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 15:16:24

"In this case should I use the method mentioned above[/]i"

I don't know the answer to that, but its certainly worth testing!

I suggest you put a deliberate Divide-by-zero error in your SProc and see if/how DTS catches it.

"Is it better to use"

In my opinion you need both, as per my example. Then your Sproc can return a [i]logical error code, if it wants to, but any SQL Error [or RAISERROR] will still be captured

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources