Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

error handling stroed proc Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:47 AM
Points: 7, Visits: 63
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
Post #1522018
Posted Wednesday, December 11, 2013 2:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1522062
Posted Wednesday, December 11, 2013 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:47 AM
Points: 7, Visits: 63
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....
Post #1522064
Posted Wednesday, December 11, 2013 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:47 AM
Points: 7, Visits: 63

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
Post #1522066
Posted Wednesday, December 11, 2013 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1522070
Posted Friday, December 13, 2013 11:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 1:25 PM
Points: 129, Visits: 301
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...
Post #1522826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse