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