Error Handling in T-SQL

Error handling is one of those things in SQL Server that just doesn’t get the attention it deserves. Even a properly constructed stored procedure can still result in error, such as primary key or unique constraint errors.

Why should you care? Consider this real-world example:

You’re a DBA monitoring a well-performing environment. You deploy a new application to production. Suddenly, performance degrades but you do not know why. You look in your error log and see a whole mess of primary key errors. Digging into your newly deployed application, you find that you are now making an extra (and unnecessary) insert to the database, which is resulting in error and causing your performance issues.

This is just one example of many. Fortunately, SQL 2005 has really simplified the error handling process with features such as the Try/Catch block.

The basic components of error handling are:

  • Try…Catch block (2005/2008)
  • Error identification
  • Transaction handling
  • Error logging (optional)
  • Error notification

As an early holiday gift, here’s a generic error handling process to get you started:

If ObjectProperty(Object_ID('dbo.dba_logError_sp'), N'IsProcedure') = 1
Begin
    Drop Procedure dbo.dba_logError_sp;
    Print 'Procedure dba_logError_sp dropped';
End;
Go
 
If ObjectProperty(Object_ID('dbo.dba_errorLog'), N'IsTable') Is Null
Begin
 
    Create Table dbo.dba_errorLog
    (         errorLog_id       int identity(1,1) 
            , errorType         char(3)     
                Constraint [DF_errorLog_errorType] Default 'sys' 
            , errorDate         datetime	
                Constraint [DF_errorLog_errorDate] Default(GetDate())
            , errorLine         int
            , errorMessage      nvarchar(4000)
            , errorNumber       int
            , errorProcedure    nvarchar(126)
            , procParameters    nvarchar(4000)
            , errorSeverity     int
            , errorState        int
            , databaseName      nvarchar(255)
        Constraint PK_errorLog_errorLogID Primary Key Clustered
        (
            errorLog_id	
        )
    );
 
    Print 'Table dba_errorLog created';
 
End;
Go
 
 
Set ANSI_Nulls On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set NoCount On;
Set Numeric_RoundAbort Off;
Set Quoted_Identifier On;
Go
 
Create Procedure dbo.dba_logError_sp
(
    /* Declare Parameters */
      @errorType            char(3)         = 'sys'
    , @app_errorProcedure   varchar(50)     = ''
    , @app_errorMessage     nvarchar(4000)  = ''
    , @procParameters       nvarchar(4000)  = ''
    , @userFriendly         bit             = 0
    , @forceExit            bit             = 1
    , @returnError          bit             = 1
)
As
/***************************************************************
    Name:       dba_logError_sp
 
    Author:     Michelle F. Ufford, http://sqlfool.com
 
    Purpose:    Retrieves error information and logs in the 
                        dba_errorLog table.
 
        @errorType = options are "app" or "sys"; "app" are custom 
                application errors, i.e. business logic errors;
                "sys" are system errors, i.e. PK errors
 
        @app_errorProcedure = stored procedure name, 
                needed for app errors
 
        @app_errorMessage = custom app error message
 
        @procParameters = optional; log the parameters that were passed
                to the proc that resulted in an error
 
        @userFriendly = displays a generic error message if = 1
 
        @forceExit = forces the proc to rollback and exit; 
                mostly useful for application errors.
 
        @returnError = returns the error to the calling app if = 1
 
    Called by:	Another stored procedure
 
    Date        Initials    Description
	----------------------------------------------------------------------------
    2008-12-16  MFU         Initial Release
****************************************************************
    Exec dbo.dba_logError_sp
        @errorType          = 'app'
      , @app_errorProcedure = 'someTableInsertProcName'
      , @app_errorMessage   = 'Some app-specific error message'
      , @userFriendly       = 1
      , @forceExit          = 1
      , @returnError        = 1;
****************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    /* Declare Variables */
    Declare	@errorNumber            int
            , @errorProcedure       varchar(50)
            , @dbName               sysname
            , @errorLine            int
            , @errorMessage         nvarchar(4000)
            , @errorSeverity        int
            , @errorState           int
            , @errorReturnMessage   nvarchar(4000)
            , @errorReturnSeverity  int
            , @currentDateTime      smalldatetime;
 
    Declare @errorReturnID Table (errorID varchar(10));
 
    /* Initialize Variables */
    Select @currentDateTime = GetDate();
 
    /* Capture our error details */
    If @errorType = 'sys' 
    Begin
 
        /* Get our system error details and hold it */
        Select 
              @errorNumber      = Error_Number()
            , @errorProcedure   = Error_Procedure()
            , @dbName           = DB_Name()
            , @errorLine        = Error_Line()
            , @errorMessage     = Error_Message()
            , @errorSeverity    = Error_Severity()
            , @errorState       = Error_State() ;
 
    End
    Else
    Begin
 
    	/* Get our custom app error details and hold it */
        Select 
              @errorNumber      = 0
            , @errorProcedure   = @app_errorProcedure
            , @dbName           = DB_Name()
            , @errorLine        = 0
            , @errorMessage     = @app_errorMessage
            , @errorSeverity    = 0
            , @errorState       = 0 ;
 
    End;
 
    /* And keep a copy for our logs */
    Insert Into dbo.dba_errorLog
    (
          errorType
        , errorDate
        , errorLine
        , errorMessage
        , errorNumber
        , errorProcedure
        , procParameters
        , errorSeverity
        , errorState
        , databaseName
	)
    OutPut Inserted.errorLog_id Into @errorReturnID
    Values
    (
          @errorType
        , @currentDateTime
        , @errorLine
        , @errorMessage
        , @errorNumber
        , @errorProcedure
        , @procParameters
        , @errorSeverity
        , @errorState
        , @dbName
    );
 
    /* Should we display a user friendly message to the application? */
    If @userFriendly = 1
        Select @errorReturnMessage = 'An error has occurred in the database (' + errorID + ')'
        From @errorReturnID;
    Else 
        Select @errorReturnMessage = @errorMessage;
 
    /* Do we want to force the application to exit? */
    If @forceExit = 1
        Select @errorReturnSeverity = 15
    Else
        Select @errorReturnSeverity = @errorSeverity;
 
    /* Should we return an error message to the calling proc? */
    If @returnError = 1
        Raiserror 
        (
              @errorReturnMessage
            , @errorReturnSeverity
            , 1
        ) With NoWait;
 
    Set NoCount Off;
    Return 0;
 
End
Go

 

You would then call this proc in the following manner:

Begin Try
 
    /* If a business logic error exists, then call this proc */
    If 1 != 1
        Execute dbo.dba_logError_sp 
              @errorType            = 'app'
            , @app_errorProcedure   = 'yourStoredProcedureName'
            , @app_errorMessage     = '1 does not equal 1!'
            , @forceExit            = 1;
 
    /* Start a new transaction */
    Begin Transaction;  
 
    /* Do something */
 
    /* If you have an open transaction, commit it */
    If @@TranCount > 0
        Commit Transaction;
 
End Try
Begin Catch
 
    /* Whoops, there was an error... rollback! */
    If @@TranCount > 0
        Rollback Transaction;
 
    /* Grab our proc parameters */
    Set @errorParameters = '@myVariable = ' + @myVariable;
 
    /* Return an error message and log it */
    Execute dbo.dba_logError_sp
        @procParameters = @errorParameters;
 
End Catch;

 

Some things to keep in mind:

  • Error handling is not a “one-size-fits-all” process. Make sure you’re handling the error appropriately for your environment.
  • Be careful when working with nested transactions; you can sometimes get unexpected results.
  • Only errors with a severity levels greater than 10 will be caught by the Catch block.
  • You can initiate an error within your stored procedure by using RaisError().

Happy coding holidays! 🙂