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.

1 comment:

  1. bug: then ', ' else ', ' end
    note: set is preferred over select for variable assignment
    problem: if you don't output the error (using print() or raiserror('blah', 0, 1) then information will be lost with nested try catch blocks
    some ideas for you:
    ALTER proc dbo.spThrowError
    as
    begin
    declare @errmsg nvarchar(2048) = N'spThrowError! Err: %d, Level: %d, State: %d, Proc: %s, Line: %d, Msg: ' + error_message()
    declare @errno int = error_number()
    declare @errseverity int = error_severity()
    declare @errstate int = error_state()
    declare @errline int = error_line()
    declare @errproc nvarchar(126) = coalesce(error_procedure(), '-')
    declare @text nvarchar(max) = '[msg] = ''' + @errmsg + ''' '
    + '[proc] = ''' + @errproc + ''' '
    + '[line] = ' + cast(@errline as nvarchar(16)) + ' '
    + '[no] = ' + cast(@errno as nvarchar(16)) + ' '
    + '[severity] = ' + cast(@errseverity as nvarchar(16)) + ' '
    + '[state] = ' + cast(@errstate as nvarchar(16))
    raiserror (@text, 0, 1)
    raiserror ( @errmsg -- msg
    , @errseverity -- severity
    , 1 -- state
    , @errno -- argument
    , @errseverity -- argument
    , @errstate -- argument
    , @errproc -- argument
    , @errline -- argument
    )
    end

    ReplyDelete