Friday, 09 July 2010

A Way to Handle Raised Errors in Both Dynamic and Static SQL Code

If you've needed a reliable way to handle errors that might be raised in both dynamic and static SQL code, here is what I've been doing:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Erland Sommarskog - http://www.sommarskog.se/error_handling_2005.html
-- Create date: 2009/11/29
-- Description: **Call this sp from within your
--              CATCH block** This
--              reraises/throws the most recent
--              error. Please see
--              http://www.sommarskog.se/error_handling_2005.html
--              for more details.
--
-- Modifications:
-- Author:      A. Jackson
-- Create date: 2010/03/11
-- Description: Renamed the sp from
--              "error_handler_sp" to
--              "dba_Error_Handler" to conform
--              to naming conventions.
--            : Documented this sp.
--            : Enhanced SQL readability.
-- Author:      A. Jackson
-- Create date: 2010/05/20
-- Description: Added the @isDynamicSQL BIT = 1
--              parameter. This now includes the
--              text '' only if
--              @isDynamicSQL is true.
-- =============================================
ALTER PROCEDURE [dbo].[dba_Error_Handler]
    @isDynamicSQL BIT = 1
AS
BEGIN
    DECLARE
        @errmsg NVARCHAR(2048),
        @severity TINYINT,
        @state TINYINT,
        @errno INT,
        @proc SYSNAME,
        @lineno INT,
        @typeOfSQL VARCHAR(15)
           
    SELECT
        @errmsg = ERROR_MESSAGE(),
        @severity = ERROR_SEVERITY(),
        @state = ERROR_STATE(),
        @errno = ERROR_NUMBER(),
        @proc = ERROR_PROCEDURE(),
        @proc = ERROR_PROCEDURE(),
        @lineno = ERROR_LINE()
       
    IF @errmsg NOT LIKE '***%'
    BEGIN
        SELECT @typeOfSQL = CASE WHEN @isDynamicSQL = 1 THEN ', '
                                 ELSE ', '
                            END
        SELECT @errmsg = '*** ' + COALESCE(QUOTENAME(@proc), @typeOfSQL) +
                         LTRIM(STR(@lineno)) + '. ErrNo ' +
                         LTRIM(STR(@errno)) + ': ' + @errmsg
        RAISERROR (@errmsg, @severity, @state)
    END
    ELSE
    BEGIN
        RAISERROR (@errmsg, @severity, @state)
    END
END


Thank you to Erland Sommarskog for allowing me to post this.