CHAPTER 4  ERRORS AND EXCEPTIONS
Error Level
The Level tag within an error message indicates a number between 1 and 25. This number can
sometimes be used to either classify an exception or determine its severity. Unfortunately, the key word
is “sometimes”: the error levels assigned by SQL Server are highly inconsistent and should generally not
be used in order to make decisions about how to handle exceptions.
The following exception, based on its error message, is of error level 15:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
The error levels for each exception can be queried from the sys.messages view, using the severity
column. A severity of less than 11 indicates that a message is a warning. If severity is 11 or greater, the
message is considered to be an error and can be broken down into the following documented
categories:
• Error levels 11 through 16 are documented as “errors that can be corrected by the
user.” The majority of exceptions thrown by SQL Server are in this range,
including constraint violations, parsing and compilation errors, and most other
runtime exceptions.
• Error levels 17 through 19 are more serious exceptions. These include out-of-
memory exceptions, disk space exceptions, internal SQL Server errors, and other
similar violations. Many of these are automatically logged to the SQL Server error
log when they are thrown. You can identify those exceptions that are logged by
examining the is_event_logged column of the sys.messages table.
• Error levels 20 through 25 are fatal connection and server-level exceptions. These
include various types of data corruption, network, logging, and other critical
errors. Virtually all of the exceptions at this level are automatically logged.
Although the error levels that make up each range are individually documented in Books Online
(http://msdn2.microsoft.com/en-us/library/ms164086.aspx), this information is inconsistent or
incorrect in many cases. For instance, according to documentation, severity level 11 indicates errors
where “the given object or entity does not exist.” However, error 208, “Invalid object name,” is a level-16
exception. Many other errors have equally unpredictable levels, and it is recommended that you do not
program client software to rely on the error levels for handling logic.

GO
In this case, although a divide-by-zero exception occurs on line 5 of the code listing itself, the
exception message will report that the exception was encountered on line 1:
(1 row(s) affected)

(1 row(s) affected)
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The reason for the reset of the line number is that GO is not actually a T-SQL command. GO is an
identifier recognized by SQL Server client tools (e.g., SQL Server Management Studio and SQLCMD) that
tells the client to separate the query into batches, sending each to SQL Server one after another. This
seemingly erroneous line number reported in the previous example occurs because each batch is sent
separately to the query engine. SQL Server does not know that on the client (e.g., in SQL Server
Management Studio) these batches are all displayed together on the screen. As far as SQL Server is
80
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
concerned, these are three completely separate units of T-SQL that happen to be sent on the same
connection.
SQL Server’s RAISERROR Function
In addition to the exceptions that SQL Server itself throws, users can raise exceptions within T-SQL by
using a function called RAISERROR. The general form for this function is as follows:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
The first argument can be an ad hoc message in the form of a string or variable, or a valid error
number from the message_id column of sys.messages. If a string is specified, it can include format
designators that can then be filled using the optional arguments specified at the end of the function call.
The second argument, severity, can be used to enforce some level of control over the behavior of

. This syntax is deprecated in SQL Server 2008 and
should not be used.
Formatting Error Messages
When defining error messages, it is generally useful to format the text in some way. For example, think
about how you might write code to work with a number of product IDs, dynamically retrieved, in a loop.
You might have a local variable called @ProductId, which contains the ID of the product that the code is
currently working with. If so, you might wish to define a custom exception that should be thrown when a
problem occurs—and it would probably be a good idea to return the current value of @ProductId along
with the error message.
In this case, there are a couple of ways of sending back the data with the exception. The first is to
dynamically build an error message string:
DECLARE @ProductId int;
SET @ProductId = 100;

/* ... problem occurs ... */

DECLARE @ErrorMessage varchar(200);
SET @ErrorMessage =
'Problem with ProductId ' + CONVERT(varchar, @ProductId);

RAISERROR(@ErrorMessage, 16, 1);
Executing this batch results in the following output:
Msg 50000, Level 16, State 1, Line 10
Problem with ProductId 100
While this works for this case, dynamically building up error messages is not the most elegant
development practice. A better approach is to make use of a format designator and to pass @ProductId as
an optional parameter, as shown in the following code listing:
DECLARE @ProductId int;
SET @ProductId = 100;


are the
same as those used by the C language’s
printf
function. For a complete list of the supported designators, see the
“RAISERROR (Transact-SQL)” topic in SQL Server 2008 Books Online.
Creating Persistent Custom Error Messages
Formatting messages using format designators instead of building up strings dynamically is a step in the
right direction, but it does not solve one final problem: what if you need to use the same error message
in multiple places? You could simply use the same exact arguments to RAISERROR in each routine in
which the exception is needed, but that might cause a maintenance headache if you ever needed to
change the error message. In addition, each of the exceptions would only be able to use the default user-
defined error number, 50000, making programming against these custom exceptions much more
difficult.
Luckily, SQL Server takes care of these problems quite nicely, by providing a mechanism by which
custom error messages can be added to sys.messages. Exceptions using these error messages can then
be raised by using RAISERROR and passing in the custom error number as the first parameter.
To create a persistent custom error message, use the sp_addmessage stored procedure. This stored
procedure allows the user to specify custom messages for message numbers over 50000. In addition to
an error message, users can specify a default severity. Messages added using sp_addmessage are scoped
at the server level, so if you have multiple applications hosted on the same server, be aware of whether
83
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
they define custom messages and whether there is any overlap—you may need to set up a new instance
of SQL Server for one or more of the applications in order to allow them to create their exceptions. When
developing new applications that use custom messages, try to choose a well-defined range in which to
create your messages, in order to avoid overlaps with other applications in shared environments.
Remember that you can use any number between 50000 and 2147483647, and you don’t need to stay in
the 50000 range.
Adding a custom message is as easy as calling sp_addmessage and defining a message number and

@msgtext = 'Problem with ProductId numbers %i, %i, %i',
84
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
@Replace = 'Replace';
GO
 Note In addition to being able to add a message and set a severity,
sp_addmessage
supports localization of
messages for different languages. The examples here do not show localization; instead, messages will be created
for the user’s default language. For details on localized messages, refer to SQL Server 2008 Books Online.
Logging User-Thrown Exceptions
Another useful feature of RAISERROR is the ability to log messages to SQL Server’s error log. This can come
in handy especially when working with automated code, such as T-SQL run via a SQL Server agent job.
In order to log any exception, use the WITH LOG option of the RAISERROR function, as in the following T-
SQL:
RAISERROR('This will be logged.', 16, 1) WITH LOG;
Note that specific access rights are required to log an error. The user executing the RAISERROR
function must either be a member of the sysadmin fixed server role or have ALTER TRACE permissions.
Monitoring Exception Events with Traces
Some application developers go too far in handling exceptions, and end up creating applications that
hide problems by catching every exception that occurs and not reporting it. In such cases it can be
extremely difficult to debug issues without knowing whether an exception is being thrown. Should you
find yourself in this situation, you can use a Profiler trace to monitor for exceptions occurring in SQL
Server.
In order to monitor for exceptions, start a trace and select the Exception and User Error Message
events. For most exceptions with a severity greater than 10, both events will fire. The Exception event will
contain all of the data associated with the exception except for the actual message. This includes the
error number, severity, state, and line number. The User Error Message event will contain the formatted
error message as it was sent to the client.

Server is still useful in some cases—and a lot of legacy code will be around for quite a while—so a quick
review is definitely warranted.
 Note If you’re following the examples in this chapter in order, make sure that you have turned off the
XACT_ABORT
setting before trying the following examples.
The @@ERROR function is quite simple: it returns 0 if the last statement in the batch did not throw an
error of severity 11 or greater. If the last statement did throw an error, it returns the error number. For
example, consider the following T-SQL:
SELECT 1/0 AS DivideByZero;
SELECT @@ERROR AS ErrorNumber;
GO
The first statement returns the following message:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
86
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
and the second statement returns a result set containing a single value, containing the error number
associated with the previous error:
ErrorNumber
8134
By checking to see whether the value of @@ERROR is nonzero, it is possible to perform some very
primitive error handling. Unfortunately, this is also quite error prone due to the nature of @@ERROR and
the fact that it only operates on the last statement executed in the batch. Many developers new to T-SQL
are quite surprised by the output of the following batch:
SELECT 1/0 AS DivideByZero;
IF @@ERROR <> 0
SELECT @@ERROR AS ErrorNumber;
GO
The first line of this code produces the same error message as before, but on this occasion, the result

Any type of exception—except for connection- or server-level exceptions—that occurs between
BEGIN TRY and END TRY will cause the code between BEGIN CATCH and END CATCH to be immediately
executed, bypassing any other code left in the try block.
As a first example, consider the following T-SQL:
BEGIN TRY
SELECT 1/0 AS DivideByZero;
END TRY
BEGIN CATCH
SELECT 'Exception Caught!' AS CatchMessage;
END CATCH
Running this batch produces the following output:
DivideByZero
------------

CatchMessage
-----------------
Exception Caught!
The interesting things to note here are that, first and foremost, there is no reported exception. We
can see that an exception occurred because code execution jumped to the CATCH block, but the exception
was successfully handled, and the client is not aware that an exception occurred. Second, notice that an
empty result set is returned for the SELECT statement that caused the exception. Had the exception not
been handled, no result set would have been returned. By sending back an empty result set, the implied
contract of the SELECT statement is honored (more or less, depending on what the client was actually
expecting).
Although already mentioned, it needs to be stressed that when using TRY/CATCH, all exceptions
encountered within the TRY block will immediately abort execution of the remainder of the TRY block.
Therefore, the following T-SQL has the exact same output as the last example:
BEGIN TRY
SELECT 1/0 AS DivideByZero;
SELECT 1 AS NoError;

ELSE
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
As expected, in this case the error number is correctly reported:
ConvertException
----------------

ErrorNumber
-----------
245
89
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
These functions, especially ERROR_NUMBER, allow for coding of specific paths for certain exceptions.
For example, if a developer knows that a certain piece of code is likely to cause an exception that can be
programmatically fixed, that exception number can be checked for in the CATCH block.
Rethrowing Exceptions
A common feature in most languages that have try/catch capabilities is the ability to rethrow exceptions
from the catch block. This means that the exception that originally occurred in the try block will be
raised again, as if it were not handled at all. This is useful when you need to do some handling of the
exception but also let the caller know that something went wrong in the routine.
T-SQL does not include any kind of built-in rethrow functionality. However, it is fairly easy to create
such behavior based on the CATCH block error functions, in conjunction with RAISERROR. The following
example shows a basic implementation of rethrow in T-SQL:
BEGIN TRY
SELECT CONVERT(int, 'ABC') AS ConvertException;
END TRY
BEGIN CATCH
DECLARE
@ERROR_SEVERITY int = ERROR_SEVERITY(),

your code will throw a timeout exception. It might make more sense to pass back a generic “data not
available” exception than to expose the actual cause of the problem to the caller. This is something that
should be decided on a case-by-case basis, as you work out optimal designs for your stored procedure
interfaces.
When Should TRY/CATCH Be Used?
As mentioned previously, the general use case for handling exceptions in T-SQL routines (such as within
stored procedures) is to encapsulate as much as possible at as low a level as possible, in order to simplify
the overall code of the application. A primary example of this is logging of database exceptions. Instead
of sending an exception that cannot be properly handled back to the application tier where it will be
logged back to the database, it probably makes more sense to log it while already in the scope of a
database routine.
Another use case involves temporary fixes for problems stemming from application code. For
instance, the application—due to a bug—might occasionally pass invalid keys to a stored procedure that
is supposed to insert them into a table. It might be simple to temporarily “fix” the problem by simply
catching the exception in the database rather than throwing it back to the application where the user will
receive an error message. Putting quick fixes of this type into place is often much cheaper than
rebuilding and redeploying the entire application.
It is also important to consider when not to encapsulate exceptions. Make sure not to overhandle
security problems, severe data errors, and other exceptions that the application—and ultimately, the
user—should probably be informed of. There is definitely such a thing as too much exception handling,
and falling into that trap can mean that problems will be hidden until they cause enough of a
commotion to make themselves impossible to ignore.
Long-term issues hidden behind exception handlers usually pop into the open in the form of
irreparable data corruption. These situations are usually highlighted by a lack of viable backups because
the situation has been going on for so long, and inevitably end in lost business and developers getting
their resumes updated for a job search. Luckily, avoiding this issue is fairly easy. Just use a little bit of
common sense, and don’t go off the deep end in a quest to stifle any and all exceptions.
Using TRY/CATCH to Build Retry Logic
An interesting example of where TRY/CATCH can be used to fully encapsulate an exception is when dealing
with deadlocks. Although it’s better to try to find and solve the source of a deadlock than to code around

IF @Retries = 0
RAISERROR('Could not complete transaction!', 16, 1);
END
ELSE
RAISERROR('Non-deadlock condition encountered', 16, 1);
BREAK;
END CATCH
END;
GO
In this example, the deadlock-prone code is retried as many times as the value of @Retries. Each
time through the loop, the code is tried. If it succeeds without an exception being thrown, the code gets
to the BREAK and the loop ends. Otherwise, execution jumps to the CATCH block, where a check is made to
ensure that the error number is 1205 (deadlock victim). If so, the counter is decremented so that the loop
can be tried again. If the exception is not a deadlock, another exception is thrown so that the caller
knows that something went wrong. It’s important to make sure that the wrong exception does not trigger
a retry.
Exception Handling and Defensive Programming
Exception handling is extremely useful, and its use in T-SQL is absolutely invaluable. However, I hope that
all readers keep in mind that exception handling is no substitute for proper checking of error conditions
before they occur. Whenever possible, code defensively—proactively look for problems, and if they can be
both detected and handled, code around them.
Remember that it’s generally a better idea to handle exceptions rather than errors. If you can predict a
condition and write a code path to handle it during development, that will usually provide a much more
robust solution than trying to trap the exception once it occurs and handle it then.
92
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
Exception Handling and SQLCLR
The .NET Framework provides its own exception-handling mechanism, which is quite separate from the
mechanism used to deal with exceptions encountered in T-SQL. So, how do the two systems interact

with invalid values:
93
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
DECLARE @HierarchyId hierarchyid = '/1/1';
DECLARE @Geography geography = 'POLYGON((0 51, 0 52, 1 52, 1 51 ,0 51))';
GO
Both of these statements will lead to CLR exceptions, reported as follows:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or
aggregate "hierarchyid":
Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse
failed because the input string '/1/1' is not a valid string representation of a
SqlHierarchyId node.
Microsoft.SqlServer.Types.HierarchyIdException:
at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)
.
Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or
aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does
not represent a valid geography instance because it exceeds a single hemisphere.
Each geography instance must fit inside a single hemisphere. A common reason for
this error is that a polygon has the wrong ring orientation.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult
errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
94
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

{
// Exception Handling code here

// Optionally, rethrow the exception
// throw new Exception("An exception occurred that couldn't be handled");
95
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
}

return result;
}
Alternatively, you could create code paths that rely on parsing the contents of ERROR_MESSAGE() to
identify the details of the original CLR exception specified in the stack trace. The exceptions generated
by the system-defined CLR types have five-digit exception numbers in the range 24000 to 24999, so can
be distilled from the ERROR_MESSSAGE() string using the T-SQL PATINDEX function. The following code
listing demonstrates this approach when applied to the hierarchyid example given previously:
DECLARE @errorMsg nvarchar(max);
BEGIN TRY
SELECT hierarchyid::Parse('/1/1');
END TRY
BEGIN CATCH
SELECT @errorMsg = ERROR_MESSAGE();
SELECT SUBSTRING(@errorMsg, PATINDEX('%: 24[0-9][0-9][0-9]%', @errorMsg) + 2,
5);
END CATCH
GO
The resulting value, 24001, relates to the specific CLR exception that occurred (“the input string is
not a valid string representation of a SqlHierarchyId node”), rather than the generic T-SQL error 6522,
and can be used to write specific code paths to deal with such an exception.

(1 row(s) affected)
Another mistake is the belief that stored procedures represent some sort of atomic unit of work,
complete with their own implicit transaction that will get rolled back in case of an exception. Alas, this is
also not the case, as the following T-SQL proves:
--Create a table for some data
CREATE TABLE SomeData
(
SomeColumn int
);
GO

--This procedure will insert one row, then throw a divide-by-zero exception
CREATE PROCEDURE NoRollback
AS
BEGIN
INSERT INTO SomeData VALUES (1);

INSERT INTO SomeData VALUES (1/0);
END;
GO

--Execute the procedure
EXEC NoRollback;
GO

--Select the rows from the table
97
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 4  ERRORS AND EXCEPTIONS
SELECT *

TRUNCATE TABLE SomeData;
GO
--This procedure will insert one row, then throw a divide-by-zero exception
CREATE PROCEDURE XACT_Rollback
AS
98
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.