December 11, 2013 at 12:24 pm
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.03initial 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
December 11, 2013 at 2:28 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2013 at 2:38 pm
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....
December 11, 2013 at 2:40 pm
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.03initial 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
December 11, 2013 at 2:53 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2013 at 11:57 am
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...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply