E. Sommarskog is the important source on this subject with a set of supremely readable articles here http://www.sommarskog.se/error-handling-I.html – covering up to Sql Server 2005.
ERROR_HandleCaughtError stored procedure
This is ES’s error handler in a form that suits me: I usually want to add some additional information to aid debugging – e.g. the values of parameters.
- I seem to be thinking of an “error” schema with the prefix I’ve given it. Probably dbo is the appropriate location (since little else would ideally end up there).
- I think I have solved the question of whether the LOG option of RAISERROR is always allowed; I’ll update this post.
- I’ve gone with ES’s 5555 return value. Mostly I don’t use the return value, but I’ve yet to form a complete stance on them. I wouldn’t trust them to indicate errors, largely because of the ambivalence with which they are treated.
- I am no longer certain why I added Execute As Caller. It might be important if I were capturing the login running the procedure (and indeed, that might be what appears in the LOG). On the other hand, it might expose the procedure to the vagaries of Caller’s permissions. Again, I will confirm and update the post.
CREATE PROCEDURE [dbo].[ERROR_HandleCaughtError]
@DebugInfo varchar(2000) = NULL
/*
2013-01-06
Reports details of an error to user.
Logs error to SQL Server Log.
IN
@DebugInfo varchar(200) - optional. Additional
debug information to append to error message.
VERSION
2014-08-19 includes database name.
*/
WITH EXECUTE AS CALLER
AS
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state tinyint,
@errno int,
@proc sysname,
@lineno int,
@dbname varchar(50)
SELECT @errmsg = error_message(), @severity = error_severity(),
@state = error_state(), @errno = error_number(),
@proc = error_procedure(), @lineno = error_line(),
@dbname = db_name()
IF @errmsg NOT LIKE N'***%'
BEGIN
-- This is the first time the error has been handled in the stack.
SELECT @errmsg = '*** Proc. ' + @dbname + '..' + coalesce(quotename(@proc), '<dynamic SQL>') +
', Ln. ' + ltrim(str(@lineno)) + '. Err. ' +
ltrim(str(@errno)) + ': ' + @errmsg +
ISNULL(' [' + @DebugInfo + ']', '')
RAISERROR(@errmsg, @severity, @state) WITH LOG
END
ELSE
BEGIN
-- The error information has already been concatenated; rethrow error. No logging required.
RAISERROR(@errmsg, @severity, @state)
END
Implementing the Error Handler
Here is a pattern showing the error handler in use in an outline stored procedure. Copy and Paste.
CREATE PROCEDURE schema.StoredProcedure_Action
/*
This is what it does.
PARAMETERS
RETURNS
0/0/0000 Initials : created
*/
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @DebugInfo varchar(2000)
BEGIN TRY
SELECT @DebugInfo = 'int_parameter=' + ISNULL(CONVERT(varchar(8), @int_parameter), 'NULL') +
' date_parameter=' + ISNULL(CONVERT(varchar(20), @date_parameter, 120), 'NULL');
-- Raise errors for any parameters that must not be Null.
IF (@int_parameter IS NULL) RAISERROR('@int_parameter is required', 16, 1)
-- Procedure Actions Here --
END TRY
BEGIN CATCH
EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
RETURN 5555
END CATCH
Discussion
All exceptions are re-thrown and take the error number of 50000. Sometimes I would prefer to raise more specific errors to help the application using the procedure – e.g. differentiating between invalid parameters (which could be corrected by the application) and invalid data like a missing lookup value (which couldn’t). Custom error numbers have to be added to sys.messages: it allows you to specify a custom formatted error message (good), but the custom entries would apply to all databases on the instance, and I don’t care to take that step just yet. A universal set of conventionalised custom errors would be the answer (which ones would you need?), but I haven’t devised one yet, and I have found little evidence of anyone else using them yet. What would happen if you loaded a third party database that defined its own conflicting custom errors?
Revisions
1. Aug 2014. Knowing the database that the error came from is undeniably useful when trawling logs. Added to this version. I haven’t yet found a way of returning the schema of the error procedure.