SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


error handling stroed proc


error handling stroed proc

Author
Message
tsandeep1407
tsandeep1407
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 88
trying to create a storeproc for error handling...so that all my team members call this sp in their catch blcoks...could it be even better than below?
CREATE PROCEDURE ims.p_KCR_Handle_error
AS

/*
==================================================================================================
Name : p_ECR_Handle_Error
Author : *************************
Description : Used for error handling in catch block
===================================================================================================

Returns : 0 for success and -1 for failure

Usage:

EXEC some_sp
EXEC outer_sp
exec ims.p_KCR_Handle_Error

Example:
drop PROCEDURE some_sp;
go
CREATE PROCEDURE some_sp AS
BEGIN TRY
SET NOCOUNT ON
SELECT 1/0
RETURN 0
END TRY
BEGIN CATCH
DECLARE @returncd int
EXEC @returncd = ims.p_ECR_Handle_Error

select @returncd
RETURN -1
END CATCH
go
===================================================================================================
IF you are using this in nested procs

CREATE PROCEDURE outer_sp AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @ResultCd int;

EXEC @ResultCd = some_sp

IF @ResultCd <> 0 BEGIN
Begin
RAISERROR('error in called proc', 16, 1)
End
END
RETURN 0
END TRY
BEGIN CATCH
EXEC ims.p_ECR_Handle_Error
RETURN -1
END CATCH
go
===================================================================================================
History:

Name Date Description
---------- ---------- -------------------------------------------------------------------------------
****** 2013.12.03 initial implementaion
========================================================================================================
*/

DECLARE @errmsg NVARCHAR(2048)
DECLARE @severity TINYINT
DECLARE @state TINYINT
DECLARE @errno INT
DECLARE @proc SYSNAME
DECLARE @lineno INT
DECLARE @ReturnCd INT;

BEGIN TRY

SET @errmsg = error_message()
SET @severity = error_severity()
SET @state = error_state()
SET @errno = error_number()
SET @proc = error_procedure()
SET @lineno = error_line()
SET @ReturnCd = 0

SELECT @errmsg = '*** ' + quotename(@proc) +
', ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg


IF @@NESTLEVEL = 2
RAISERROR(@errmsg, @severity, @state)
ELSE PRINT(@errmsg)

RETURN 0

END TRY

BEGIN CATCH

SET @ErrMsg = error_message();
SET @ReturnCd = error_number();
SET @severity = error_severity();
SET @state = error_state();

IF @@nestlevel = 1
RAISERROR(@ErrMsg,@severity,@state)


RETURN -1

END CATCH
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65344 Visits: 17980
tsandeep1407 (12/11/2013)
trying to create a storeproc for error handling...so that all my team members call this sp in their catch blcoks...could it be even better than below?


Did you try this? I don't think is going to do what you want it to do. The error being raised will be empty because you don't capture the values in scope of the original error. Try running your code and you will see what I mean.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tsandeep1407
tsandeep1407
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 88
you are right....i had the wrong nestlevels.....if i comment out the nestlevels it is working.....but raiserrror() is executing from catch block not the try block....
tsandeep1407
tsandeep1407
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 88
updated one.............
AS

/*
==================================================================================================
Name : p_ECR_Handle_Error
Author : *************************
Description : Used for error handling in catch block
===================================================================================================

Returns : 0 for success and -1 for failure

Usage:

EXEC some_sp
EXEC outer_sp
exec ims.p_KCR_Handle_Error

Example:
drop PROCEDURE some_sp;
go
CREATE PROCEDURE some_sp AS
BEGIN TRY
SET NOCOUNT ON
SELECT 1/0
RETURN 0
END TRY
BEGIN CATCH
DECLARE @returncd int
EXEC @returncd = ims.p_ECR_Handle_Error

select @returncd
RETURN -1
END CATCH
go
===================================================================================================
IF you are using this in nested procs

CREATE PROCEDURE outer_sp AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @ResultCd int;

EXEC @ResultCd = some_sp

IF @ResultCd <> 0 BEGIN
Begin
RAISERROR('error in called proc', 16, 1)
End
END
RETURN 0
END TRY
BEGIN CATCH
EXEC ims.p_ECR_Handle_Error
RETURN -1
END CATCH
go
===================================================================================================
History:

Name Date Description
---------- ---------- -------------------------------------------------------------------------------
****** 2013.12.03 initial implementaion
========================================================================================================
*/

DECLARE @errmsg NVARCHAR(2048)
DECLARE @severity TINYINT
DECLARE @state TINYINT
DECLARE @errno INT
DECLARE @proc SYSNAME
DECLARE @lineno INT
DECLARE @ReturnCd INT;

BEGIN TRY

SET @errmsg = error_message()
SET @severity = error_severity()
SET @state = error_state()
SET @errno = error_number()
SET @proc = error_procedure()
SET @lineno = error_line()
SET @ReturnCd = 0

SELECT @errmsg = '*** ' + quotename(@proc) +
', ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg



RAISERROR(@errmsg, @severity, @state)


RETURN 0

END TRY

BEGIN CATCH


RAISERROR(@ErrMsg,@severity,@state)


RETURN -1

END CATCH
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65344 Visits: 17980
tsandeep1407 (12/11/2013)
...but raiserrror() is executing from catch block not the try block....


HUH?

You have a RAISERROR in both the TRY AND the CATCH.

I would think that if you are trying to create an error trapping process you probably would want to log the errors in addition to handling them and selecting the error message. :-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ChrisCarsonSQL
ChrisCarsonSQL
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 487
tsandeep, I had a client several months back where we needed some standard error handling logic.

My advice, spend a few hours Googling/Binging/whatevering for error-handling in stored procedures.

There are several outstanding examples that are easily understood and easily implemented.

My experience was that trying to write my own code was a waste of time since there was code already written by people smarter than me that did nearly everything I needed.

I'd suggest starting with Erland Sommarskog's excellent treatment on the subject.

Not posting any links. A) Search yourself and you will find other useful references. B) I'm lazy. C) My query is finished so I have to review my test results.

Good luck :-)

_____________________________

Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search