/*
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 from the CODE DOWNLOAD link above.
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 */
---------------------------------------
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 */
---------------------------------------
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.
*/
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 occurrs.
-- This means that if we use the exact same code as above, but check the
-- @@ERROR function
-- a second time, it will be different.
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.
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.
*/
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:
*/
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:
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 elminate 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:
*/
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:
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.
--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.
*/
-- 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 elipsis.
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 effecient manner in SQL Server 2005. This from the Books
Online:
*/
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.
*/
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:
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.
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.
*/
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:
(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:
*/
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:
*/
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.
*/
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.
*/
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.
*/
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
