The Requirement
The idea is to have a TSQL reusable pattern that can be applied to all of your stored procedures to handle errors in a consistent manner, by consistent I mean:
· Bubble up the original error to the caller regardless of the nesting level (no information should be lost
· Handle transaction scope and rollback consistently regardless of the nesting level
· Standard error formatting
I don’t take full credit for this pattern, I came up with it a few years ago after looking at a couple of great posts and customizing them to my needs. Unfortunately I don’t have the references anymore.
Existing drawbacks
· Without a pattern we end up with a lot of redundant code
· Not enough information provided when an error is thrown
· Code is somewhat difficult to read and to maintain, especially if it’s done by multiple developers
· Procedures that use transactions are not designed to handle them on their own or as part of another one. In other words, most of these procedures are not 100% reusable today.
· Overall, there are logic errors in the “catch” statements
By applying this pattern and using a single stored procedure to handle/format the errors, we can support “bubbling” up exceptions without losing information and still keep our stored proc code standard and clean. This pattern supports nested operations even with transactions.
Design Guidelines
· Procedures can run on their own or can be nested.
· If multiple procedures are nested, and they use transactions, only the first one should open it and commit it, this means having only 1 transaction open for the entire scope.
· If a procedure catches an error, and a transaction is open, it will it roll it back only if it opened it.
· Concentrate all the error handling/formatting code in a single reusable procedure.
· Independently of the nesting level, the error handling procedure should provide enough information about the error, e.g. which procedure threw it, error message (including custom messages), error number, line number and what other procedures did propagate through.
· This pattern does NOT implement logging, just the necessary plumbing code to properly bubble up exceptions without losing information.
A reminder about transactions
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1, the COMMIT statement decreases it by 1 and the ROLLBACK sets it to 0 always. Therefore, if a procedure needs to execute its code in a transaction, it should know whether to open it, commit it and/or roll it back.
A word of caution when using nested procedures with transactions!
In general, I recommend you put special care when working with nested stored procedures that use transactions. Remember that transaction scopes should be kept as small as possible, this is a good rule to avoid deadlocks and other problems.
The generic stored procedure template
Use this simple template in all of your stored procedures, it’s simply using a variable to track if a transaction has already been opened by the parent caller; if it hasn’t , and a transaction is needed, it will open it. This allows the procedure to be used in a standalone or nested fashion.
CREATE PROCEDURE [dbo].[YOUR_STORED_PROC_NAME]
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
-- ==============
-- TEMPLATE START
-- ==============
BEGIN TRY
DECLARE @LocalTranStarted bit
SELECT @LocalTranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @LocalTranStarted = 1
END
ELSE
SET @LocalTranStarted = 0
-- ===================================
-- The procedure's main code goes here
-- ===================================
IF( @LocalTranStarted = 1 )
BEGIN
SET @LocalTranStarted = 0
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
-- Increase the size if you’ll be using longer names
DECLARE @Procedure VARCHAR(128)
SET @Procedure = OBJECT_NAME(@@PROCID)
EXEC dbo.RaiseErrorInfo
@Procedure = @Procedure,
@AdditionalInfo = 'bla bla bla'
END CATCH
-- ============
-- TEMPLATE END
-- ============
END
The Error Handling stored procedure
This stored procedure is designed to capture the error thrown by the offending procedure, wrap it around additional context information and re-throw the new wrapped error message. If all the stored procedures follow the above template, the error will be “bubbled up” all the way to the original caller. All the error information will be preserved.
NOTE: You’re not bound to the information gathered by this procedure only or to the way it’s formatted, this is an example; by all means, add/remove information as you see fit or format it in a way it better suits you.
CREATE PROCEDURE [dbo].[RaiseErrorInfo]
@Procedure NVARCHAR(255),
@AdditionalInfo NVARCHAR(255) = NULL
AS
BEGIN
DECLARE @errno INT,
@errmsg VARCHAR(4000),
@errline INT,
@errstate INT,
@errseverity INT,
@INSERTED_IDENTITY_ID INT
DECLARE @CreateUserName VARCHAR(128),
@CreateMachineName VARCHAR(128)
SET @CreateUserName = CONVERT(SYSNAME, CURRENT_USER)
SET @CreateMachineName = HOST_NAME()
SET @errno = ERROR_NUMBER()
SET @errseverity = ERROR_SEVERITY()
SET @errstate = ISNULL(NULLIF(ERROR_STATE(), 0), 1)
SET @errline = ERROR_LINE()
-- Wrap all the context information around the original error message
SET @errmsg = '[Error in procedure ' + ISNULL(@Procedure,
ERROR_PROCEDURE()) + ': ' +
ISNULL(@AdditionalInfo, '') + ' -> ~' + ERROR_MESSAGE() +
'~, Error number: ' + CAST(@errno AS NVARCHAR) + ', Line: ' +
CAST(@errline AS NVARCHAR) + ' (' + @CreateUserName + ', ' +
@CreateMachineName + ')]'
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
RAISERROR (@errmsg, @errseverity, @errstate)
END
