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:
Thank you to Erland Sommarskog for allowing me to post this.
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
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 '
-- @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.
bug: then ', ' else ', ' end
ReplyDeletenote: 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