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! 
