Error handling in SQL Server breaks down into two very distinct situations: you’re handling errors because you’re in SQL Server 2005 or you’re not handling errors because you’re in SQL Server 2000. What’s worse, not all errors in SQL Server, either version, can be handled. I’ll specify where these types of errors come up in each version.
The different types of error handling will be addressed in two different sections. ‘ll be using two different databases for the scripts as well, [pubs] for SQL Server 2000 and [AdventureWorks] for SQL Server 2005.
I’ve broken down the scripts and descriptions into sections. Here is a Table of Contents to allow you to quickly move to the piece of code you’re interested in. Each piece of code will lead with the server version on which it is being run. In this way you can find the section and the code you want quickly and easily.
As always, the intent is that you load this workbench into Query Analyser or Management Studio and try it out for yourself! The workbench script is available in the downloads at the bottom of the article.
- GENERATING AN ERROR
- SEVERITY AND EXCEPTION TYPE
- TRAP AN ERROR
- USING RAISERROR
- RETURNING ERROR CODES FROM STORED PROCEDURES
- TRANSACTIONS AND ERROR TRAPPING
- EXTENDED 2005 ERROR TRAPPING
SQL Server 2000 – GENERATING AN ERROR
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE pubs GO UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' /* This will generate an error: Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the CHECK constraint "CK__authors__zip__7F60ED59". The conflict occurred in database "pubs", table "dbo.authors", column 'zip'. |
SQL Server 2005 – GENERATING AN ERROR
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE AdventureWorks; GO UPDATE HumanResources.Employee SET MaritalStatus = 'H' WHERE EmployeeID = 100; /* This generates a familiar error: Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the CHECK constraint "CK_Employee_MaritalStatus". The conflict occurred in database "AdventureWorks", table "HumanResources.Employee", column 'MaritalStatus'. The statement has been terminated. |
SQL Server 2000 AND 2005 – ERROR SEVERITY AND EXCEPTION TYPE
The error message provides several pieces of information:
- Msg
- A message number identifies the type fo error. Can up to the value of 50000. From that point forward custom user defined error messages can be defined.
- Level
-
The severity level of the error.
- 10 and lower are informational.
- 11-16 are errors in code or programming, like the error above.
- Errors 17-25 are resource or hardware errors.
- Any error with a severity of 20 or higher will terminate the connection (if not the server).
- Line
- Defines which line number the error occurred on and can come in extremely handy when troubleshooting large scripts or stored procedures.
- Message Text
- The informational message returned by SQL Server.
Error messages are defined and stored in the system table sysmessages.
SQL Server 2000 – CATCH AN ERROR
SQL Server 2000 does not allow us to stop this error being returned, but we can try to deal with it in some fashion. The core method for determining if a statement has an error in SQL Server 2000 is the @@ERROR value. When a statement completes, this value is set.
If the value equals zero(0), no error occured. Any other value was the result of an error.
The following TSQL will result in the statement ‘A constraint error has occurred’ being printed,as well as the error.
|
1 2 3 4 5 6 7 8 |
USE pubs GO UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' IF @@ERROR = 547 PRINT 'A constraint error has occurred' GO |
@@ERROR is reset by each and every statement as it occurs. This means that if we use the exact same code as above, but check the @@ERROR function a second time, it will be different.
|
1 2 3 4 5 6 7 |
UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' IF @@ERROR = 547 PRINT 'A constraint error has occurred. Error Number:' + CAST(@@ERROR AS VARCHAR) GO |
You will see the error number as returned by the @@ERROR statement as being zero(0), despite the fact that we just had a clearly defined error.
The problem is, while the UPDATE statement did in fact error out, the IF statement executed flawlessly and @@ERROR is reset after each and every statement in SQL Server.
In order to catch and keep these errors, you need to capture the @@ERROR value after each execution.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @err INT UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err = 547 PRINT 'A constraint error has occurred. Error Number:' + CAST(@err AS VARCHAR) GO |
Now we can capture the error number and refer to it as often as needed within the code.
SQL Server 2005 – CATCH AN ERROR
While @@ERROR is still available in SQL Server 2005, a new syntax has been added to the T-SQL language, as implemented by Microsoft: TRY... CATCH.
This allows us to finally begin to perform real error trapping.
|
1 2 3 4 5 6 7 8 |
BEGIN TRY UPDATE HumanResources.Employee SET MaritalStatus = 'H' WHERE EmployeeID = 100; END TRY BEGIN CATCH PRINT 'Error Handled'; END CATCH |
While there is an error encountered in the code, none is returned to the calling function. In fact, all that will happen in this case is the string 'Error Handled' is returned to the client.
We have actually performed the function of error trapping within TSQL.
There are a number of issues around the use of TRY...CATCH that have to be dealt with, which we will cover later. For example, simply having a TRY...CATCH statement is not enough.
Consider this example:
|
1 2 3 4 5 6 7 8 9 10 11 |
UPDATE HumanResources.Employee SET ContactID = 19978 WHERE EmployeeID = 100; BEGIN TRY UPDATE HumanResources.Employee SET MaritalStatus = 'H' WHERE EmployeeID = 100; END TRY BEGIN CATCH PRINT 'Error Handled'; END CATCH |
The second error is handled, but the first one is not and we would see this error returned to client application:
|
1 2 3 4 5 |
Msg 547, LEVEL 16, State 0, Line 1 The UPDATE statement conflicted WITH the FOREIGN KEY CONSTRAINT "FK_Employee_Contact_ContactID" The conflict occurred IN DATABASE "AdventureWorks" TABLE "Person.Contact" COLUMN 'ContactID'. |
To eliminate this problem place multiple statements within the TRY statement.
SQL Server 2000 – USING RAISERROR
The RAISERROR function is a mechanism for returning to calling applications errors with your own message. It can use system error messages or custom error messages. The basic syntax is easy:
|
1 |
RAISERROR ('You made a HUGE mistake',10,1) |
To execute RAISERROR you’ll either generate a string, up to 400 characters long, for the message, or you’ll access a message by message id from the master.dbo.sysmessages table.
You also choose the severity of the error raised. Severity levels used in RAISERROR will behave exactly as if the engine itself had generated the error. This means that a SEVERITY of 20 or above will terminate the connection. The last number is an arbitrary value that has to be between 1 and 127.
You can format the message to use variables. This makes it more useful for communicating errors:
|
1 |
RAISERROR('You broke the server: %s',10,1,@@SERVERNAME) |
You can use a variety of different variables. You simply have to declare them by data type and remember that, even with variables, you have a 400 character limit. You also have some formatting options.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Unsigned Integer RAISERROR('The current error number: %u',10,1,@@ERROR) --String RAISERROR('The server is: %s',10,1,@@SERVERNAME) --Compound String & Integer & limit length of string to first 5 --characters RAISERROR('The server is: %.5s. The error is: %u',10,1, @@SERVERNAME,@@ERROR) --String with a minimum and maximum length and formatting to left RAISERROR('The server is: %-7.3s',10,1,@@SERVERNAME) |
A few notes about severity and status. Status can be any number up to 127 and you can make use of it on your client apps. Setting the Status to 127 will cause ISQL and OSQL to return the error number to the operating environment.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- To get the error into the SQL Server Error Log RAISERROR('You encountered an error',18,1) WITH LOG -- To immediately return the error to the application RAISERROR('You encountered an error',10,1) WITH NOWAIT -- That also flushes the output buffer so any pending PRINT statements, -- etc., are cleared. -- To use RAISERROR as a debug statement RAISERROR('I made it to this part of the code',0,1) |
SQL SERVER 2005 – USING RAISERROR
The function of RAISERROR in SQL Server 2005 is largely the same as for SQL 2000. However, instead of 400 characters, you have 2047. If you use 2048 or more, then 2044 are displayed along with an ellipsis.
RAISERROR will cause the code to jump from the TRY to the CATCH block.
Because of the new error handling capabilities, RAISERROR can be called in a more efficient manner in SQL Server 2005. This from the Books Online:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
BEGIN TRY RAISERROR('Major error in TRY block.',16,1); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT; SET @ErrorMessage = ERROR_MESSAGE(); SET @ErrorSeverity = ERROR_SEVERITY(); SET @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState); END CATCH; |
SQL Server 2000 – RETURNING ERROR CODES FROM STORED PROCEDURES
Stored procedures, by default, return the success of execution as either zero or a number representing the failure of execution, but not necessarily the error number encountered.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE dbo.GenError AS UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' GO DECLARE @err INT EXEC @err = GenError SELECT @err |
This will cause an error and the SELECT statement will return a non-zero value. On my machine, -6. In order take control of this, modify the procedure as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ALTER PROCEDURE dbo.GenError AS DECLARE @err INT UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err <> 0 RETURN @err ELSE RETURN 0 GO DECLARE @err INT EXEC @err = GenError SELECT @err |
This time the SELECT @err statement will return the 547 error number in the results. With that, you can begin to create a more appropriate error handling routine that will evolve into a coding best practice within your organization.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
ALTER PROCEDURE dbo.GenError AS DECLARE @err INT UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err <> 0 BEGIN IF @err = 547 RAISERROR('Check Constraint Error occurred',16,1) ELSE RAISERROR('An unspecified error has occurred.',10,1) RETURN @err END ELSE RETURN 0 GO |
SQL Server 2005 – RETURNING ERROR CODES FROM STORED PROCEDURES
In order to appropriately handle errors you to know what they are. You may also want to return the errors to the calling application. A number of new functions have been created so that you can appropriately deal with different errors, and log, report, anything you need, the errors that were generated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE PROCEDURE GenErr AS BEGIN TRY UPDATE HumanResources.Employee SET ContactID = 19978 WHERE EmployeeID = 100; END TRY BEGIN CATCH PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); PRINT 'Error Message: ' + ERROR_MESSAGE(); PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR); PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); PRINT 'Error Proc: ' + ERROR_PROCEDURE(); END CATCH GO DECLARE @err INT; EXEC @err = GenErr; SELECT @err; |
When you run the code above, you should receive this on the client, in the message, with a non-zero number in the result set:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
(0 row(s) affected) Error Number: 547 Error Message: The UPDATE statement conflicted WITH the FOREIGN KEY CONSTRAINT "FK_Employee_Contact_ContactID" The conflict occurred IN DATABASE "AdventureWorks" TABLE "Person.Contact" COLUMN 'ContactID'. Error Severity: 16 Error State: 0 Error Line: 4 Error Proc: GenErr |
In other words, everything you need to actually deal with errors as they occur.
You’ll also notice that the procedure returned an error value (non-zero) even though we didn’t specify a return code. You can still specify a return value as before if you don’t want to leave it up to the engine.
SQL Server 2000 – TRANSACTIONS AND ERROR TRAPPING
The one area of control we do have in SQL Server 2000 is around the transaction. In SQL Server 2000 you can decide to rollback or not, those are your only options. You need to make decision regarding whether or not to use XACT_ABORT. Setting it to ON will cause an entire transaction to terminate and rollback in the event of any runtime error. if you set it to OFF, then in some cases you can rollback the individual statement within the transaction as opposed to the entire transaction.
Modify the procedure to handle transactions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
ALTER PROCEDURE dbo.GenError AS DECLARE @err INT BEGIN TRANSACTION UPDATE dbo.authors SET zip = '90210' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err <> 0 BEGIN IF @err = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' ROLLBACK TRANSACTION RETURN @err END UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err <> 0 BEGIN IF @err = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' ROLLBACK TRANSACTION RETURN @err END ELSE BEGIN COMMIT TRANSACTION RETURN 0 END GO DECLARE @err INT EXEC @err = GenError SELECT zip FROM dbo.authors WHERE au_id = '807-91-6654' |
Since the above code will generate an error on the second statement, the transaction is rolled back as a unit. Switch to the results in order to see that the zip code is, in fact, still 90210. If we wanted to control each update as a seperate statement, in order to get one of them to complete, we could encapsulate each statement in a transaction:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
ALTER PROCEDURE dbo.GenError AS DECLARE @err INT BEGIN TRANSACTION UPDATE dbo.authors SET zip = '90210' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err <> 0 BEGIN IF @err = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' ROLLBACK TRANSACTION RETURN @err END ELSE COMMIT TRANSACTION BEGIN TRANSACTION UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' SET @err = @@ERROR IF @err <> 0 BEGIN IF @err = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' ROLLBACK TRANSACTION RETURN @err END ELSE BEGIN COMMIT TRANSACTION RETURN 0 END GO DECLARE @err INT EXEC @err = GenError SELECT zip FROM dbo.authors WHERE au_id = '807-91-6654' |
In this case then, the return value will be ‘90210’ since the first update statement will complete successfully. Be sure that whatever mechanism you use to call procedures does not itself begin a transaction as part of the call or the error generated will result in a rollback, regardless of the commit within the procedure. In the next example, we’ll create a transaction that wraps the other two transactions, much as a calling program would. If we then check for errors and commit or rollback based on the general error state, it’s as if the inner transaction that was successful never happened, as the outer transaction rollback undoes all the work within it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
ALTER PROCEDURE dbo.GenError AS DECLARE @err1 INT DECLARE @err2 INT BEGIN TRANSACTION BEGIN TRANSACTION UPDATE dbo.authors SET zip = '90211' WHERE au_id = '807-91-6654' SET @err1 = @@ERROR IF @err1 <> 0 BEGIN IF @err1 = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' ROLLBACK TRANSACTION RETURN @err1 END ELSE COMMIT TRANSACTION BEGIN TRANSACTION UPDATE dbo.authors SET zip = '!!!' WHERE au_id = '807-91-6654' SET @err2 = @@ERROR IF @err2 <> 0 BEGIN IF @err2 = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' ROLLBACK TRANSACTION RETURN @err2 END ELSE BEGIN COMMIT TRANSACTION RETURN 0 END IF (@err1 <> 0) OR (@err2 <> 0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION GO DECLARE @err INT EXEC @err = GenError SELECT zip FROM dbo.authors WHERE au_id = '807-91-6654' |
SQL Server 2005 – TRANSACTIONS AND ERROR TRAPPING
The new error handling changes how transactions are dealt with. You can now check the transaction state using XACT_STATE() function. Transactions can be:
- Closed (equal to zero (0))
- Open but unable to commit (-1)
- Open and able to be committed (1)
From there, you can make a decision as to whether or not a transaction is committed or rolled back. XACT_ABORT works the same way.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
ALTER PROCEDURE GenErr AS BEGIN TRY BEGIN TRAN UPDATE HumanResources.Employee SET ContactID = 1/0 WHERE EmployeeID = 100; COMMIT TRAN END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRAN; RETURN ERROR_NUMBER(); END ELSE IF (XACT_STATE()) = 1 BEGIN --it now depends on the type of error or possibly the line number --of the error IF ERROR_NUMBER() = 8134 BEGIN ROLLBACK TRAN; RETURN ERROR_NUMBER(); END ELSE BEGIN COMMIT TRAN; RETURN ERROR_NUMBER(); END END END CATCH GO DECLARE @err INT; EXEC @err = GenErr; SELECT @err; |
SQL Server 2005 – EXTENDED 2005 ERROR TRAPPING
With the new TRY...CATCH construct, it’s finally possible to do things about errors, other than just return them. Take for example the dreaded deadlock. Prior to SQL Server 2005, the best you could hope for was to walk through the error messages stored in the log recorded by setting TRACEFLAG values. Now, instead, you can set up a retry mechanism to attempt the query more than once.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
ALTER PROCEDURE GenErr AS DECLARE @retry AS tinyint, @retrymax AS tinyint, @retrycount AS tinyint; SET @retrycount = 0; SET @retrymax = 2; SET @retry = 1; WHILE @retry = 1 AND @retrycount <= @retrymax BEGIN SET @retry = 0; BEGIN TRY UPDATE HumanResources.Employee SET ContactID = ContactID WHERE EmployeeID = 100; END TRY BEGIN CATCH IF (ERROR_NUMBER() = 1205) BEGIN SET @retrycount = @retrycount + 1; SET @retry = 1; END END CATCH END GO DECLARE @err INT; EXEC @err = GenErr; SELECT @err; |
Load comments