SQL Server Transaction Exception Handling Best Practice

Error handling in sql server was a pain before SQL Server 2005. Starting from  Sql Server 2005 try ... catch blocks was introduced. It was not only giving us a beauty of coding, but greatly improved our development performance. It comes with the following build-in functions:

• ERROR_NUMBER()
• ERROR_SEVERITY()
• ERROR_STATE()
• ERROR_PROCEDURE()
• ERROR_LINE()
• ERROR_MESSAGE()

When these functions is called in a CATCH block, they will returns the error information.

After working on many projects, I found a best pattern for error handling in Sql Server as the following steps.

1. Create a log table to store error information catch by try...catch blocks,

CREATE table sys_log(
    id int identity primary key,
    procName varchar(255),
    essionID int,
    errorNumber int,
    severity smallint,
    errorState smallint,
    errorLine int,
    description nvarchar(255),
    info nvarchar(max),
    mslangid smallint default 1033, --1033 English, 2052 Chinese
    timeStemp datetime default getdate())
GO

2. create a stored procedure to catch and log error information:

CREATE proc sys_LogError @procname sysname, @info nvarchar(max)='' AS
    INSERT INTO sys_log(ProcName,SessionID,ErrorNumber,Severity,ErrorState,ErrorLine,Description,info) values (
        @procname,
        @@spid,
        ERROR_NUMBER(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_LINE(),
        ERROR_MESSAGE(),
        @info
    )

3. in all stored procedures that need to handle exepctions in transaction using this template:

CREATE proc proc_name AS
BEGIN tran
BEGIN try

    -- do something
    print 1/0
    commit tran

END try
BEGIN catch
    rollback tran
    declare @info nvarchar(max)='@table='+@table+', @urls='+@urls
    declare @procname sysname = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
    exec sys_LogError @procname,@info
    ---- re-throw exeception if you need
    --declare @msg      nvarchar(2048) = error_message()
    --declare @severity smallint       = error_severity()
    --declare @state    smallint       = error_state()
    --raiserror (@msg, @severity, @state)
END catch

ERROR_MESSAGE() can be referenced with MASTER.dbo.sysmessages table, mslangid is for identifying language used for the message.

select * from MASTER.dbo.sysmessages where msglangid=1033

It is convenient for debugging and analysis with the log data.

At last, you could creat your own process to handle database error. Enjoy!

Author: IT Team