Stored procedure to handle and re-throw ms-sql errors when executing SPs:
/************************************************************
* AUTHOR: Nitin Sawant
* DATE: Feb 03, 2012
* DESCRIPTION: re-raise the error message
* REF: http://www.sommarskog.se/error_handling_2005.html
************************************************************/
CREATE PROCEDURE SP_Handle_Error
AS
BEGIN
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state tinyint,
@errno int,
@proc sysname,
@lineno int
SELECT @errmsg = error_message(), @severity = error_severity(), -- 10
@state = error_state(), @errno = error_number(),
@proc = error_procedure(), @lineno = error_line()
IF @errmsg NOT LIKE '***%' -- 11
BEGIN
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '') +
', Line ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg
RAISERROR(@errmsg, @severity, @state)
END
ELSE
RAISERROR(@errmsg, @severity, @state)
END
Usage:
/****************************************************
* AUTHOR: Nitin Sawant
* DATE: Feb 03, 2012
* DESCRIPTION: Divide by zero exception
* URL: http://www.nitinsawant.com
****************************************************/
CREATE PROCEDURE SP_Test
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
SET NOCOUNT ON --prevents SQL Server from sending row counts from INSERT, UPDATE, DELETE and MERGE statements to the client
SET XACT_ABORT ON --make sure that any user-defined transaction is rolled back
--Do your custum stuff
SELECT 10/0 --divide by zero exception
END TRY
BEGIN CATCH
--PRINT @@TRANCOUNT
IF @@TRANCOUNT > 0 --check for open transactions and roll them back
ROLLBACK TRANSACTION;
EXEC SP_Handle_Error --re-raise the error message
RETURN 55555 --safety precaution to make sure that your procedure returns a non-zero value if no called caught the error raised in SP_Handle_Error
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
GO
EXEC SP_Test
Output:
Msg 50000, Level 16, State 1, Procedure SP_Handle_Error, Line 20
*** [SP_Test], Line 10. Errno 8134: Divide by zero error encountered.
