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! 🙂
nice post. this is often one of the things i find lacking in dev code and i try to explain why it is so important. typically it takes an “incident” in order to hammer home the point.
Those code boxes give very strange results in Google Reader.
Thanks for the head’s up, Rob! I’ll take a look and see if I can fix it.
Thanks for a great reminder about my lack of error handling. It lays out a great method for SQL2005. Too bad I am still stuck in 2000 with most of my environmnents, but there are ways to handle it there as well.
In the code above I think I’m writing in saying that whoever called the sproc where the error occurred would never know about the error – hence they would assume that whatever activity they were doing had been committed. This strikes me as being a very bad thing.
There needs to be a way of reporting back to the caller than error occurred. Logging the error is all well and good (and very much best practice) but you MUST report back to the caller. As a rule I catch the error, log it, and then rethrow it back to the caller and have it handled at the top of the call stack – probably by reporting it back to the person that instigated the activity.
-Jamie
Hi Jamie,
The error proc, dba_logError_sp , will indeed return the error to the calling app if @returnError = 1, which is the default behavior.
/* Should we return an error message to the calling proc? */
IF @returnError = 1
RAISERROR
(
@errorReturnMessage
, @errorReturnSeverity
, 1
) WITH NOWAIT;
The reason I wrote the return section this way is that I’ve used the error logging proc as a form a of debug; if a condition occurs that I want to be logged but I don’t want to return an error, I set @returnError = 1 and @forceExit = 0.
To your point, you could modify the error proc to return -1 and have your application check for and handle errors based on the return value. This just depends on your application preferences.
Pingback: links for 2009-01-08 « News to Me
Pingback: Monitoring Process for Performance Counters : SQL Fool
Hi Michelle,
This is quite a bit after your post but I ran across it. Nice post, good information. I was wondering if you could post or email an example of the contents of the dba_logError_sp stored procedure?
I have a good handle on catching errors in my procs but the logic that goes into logging them and what to do next is a little less clear. It seems most of this unclearness is from what this stored proc actually does.
Thanks.
Pingback: Error handling in T-SQL « 資訊與工作
Hi Steve,
I just found your post and implemented it. The proc where I built it in runs fine, but your error-handling ALWAYS brings up an error:
“Meldung 50000, Ebene 15, Status 1, Prozedur dba_logError_sp, Zeile 152
Zeichenfolgen- oder Binärdaten würden abgeschnitten.”
which means something like
“Message 50000, flat 15, status 1, procedure dba_logError_sp, row 152
String or binary data would be truncated.
Then I just set the declaration of @returnError to 0 and there was no error at all. Which is not the purpose of an error-handling.
Where could I have done something wrong?
Best regards from Vienna/Austria!
Martin
I’m trying to figure out how to catch an error and then NOT roll back the transaction, but instead simply skip inserting the erroneous record – but still notify about it. Is this appropriate for that, or do I need to fiddle with a MaxErrors setting or some such?
Thanks,
Rebeccah
Pingback: Less Than Dot - Blog - Awesome
Pingback: Less Than Dot - Blog - Awesome