Chapter 6. Error handling in SQL Server and applications
Prior to SQL Server 2005, error handling was limited to testing @@ERROR after each statement. This required users to write lots of similar code to do error handling. The introduction of TRY...CATCH blocks in Transact-SQL (T-SQL) gives the developer more options to detect and handle errors. It is now possible to completely consume errors on the server. Or if you’d like, you can return specific errors back to the client.
The .NET development languages also have a series of classes for SQL Server error handling. These can be used to process errors and capture informational messages from SQL Server. The combination of TRY...CATCH on the server and the SQL Server–specific error handling on the client give developers many options to capture errors and handle them appropriately.
Handling errors inside SQL Server
Consider the following T-SQL statements that generate an error:
Listing 1. Error sent to SQL Server Management Studio
SELECT [First] = 1
SELECT [Second] = 1/0
SELECT [Third] = 3
This returns the following result to SQL Server Management Studio:
First
-----------
1
Second
-----------
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
Third
-----------
3
The error message from the second SELECT statement is displayed and then the third SELECT statement executes. This error would’ve been returned to a client application.
Now consider the same three SELECT statements inside a TRY...CATCH block, as shown in listing 2.
Listing 2. T-SQL statements in a TRY...CATCH block
BEGIN TRY
SELECT [First] = 1
SELECT [Second] = 1/0
SELECT [Third] = 3
END TRY
BEGIN CATCH
PRINT 'An error occurred'
END CATCH
This will produce the following result:
First
-----------
1
Second
-----------
An error occurred
The T-SQL statements inside the TRY block will execute until an error is encountered, and then execution will transfer to the code inside the CATCH block. The first SELECT statement executes properly. The second statement generates an error. You can see that it returned the column header but not any resultset. The third SELECT statement didn’t execute because control had been passed to the CATCH block. Inside the CATCH block, the PRINT statement is executed.
The use of a TRY...CATCH block also prevents the error from being returned to a client. In effect, the CATCH block consumed the error and no error was returned to a client application.
A TRY...CATCH block has some limitations:
- A TRY...CATCH block can’t span batches.
- Severity levels of 20 or higher will not be caught because the connection is closed. We will cover severity levels in more detail shortly.
- Compile errors will not be caught because the batch never executes.
- Statement recompilation errors will not be caught.
Returning information about the error
Each error that’s captured in a CATCH block has properties that we can examine using system-provided functions. These functions only return values inside a CATCH block. If they’re called outside a CATCH block, they return NULL. A stored procedure called from within a CATCH block will have access to the functions. These functions are illustrated in listing 3.
Listing 3. Outputting error properties with system-provided functions
BEGIN TRY
SELECT [Second] = 1/0
END TRY
BEGIN CATCH
SELECT [Error_Line] = ERROR_LINE(),
[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),
[Error_State] = ERROR_STATE()
SELECT [Error_Message] = ERROR_MESSAGE()
END CATCH
This returns the following result:
Second
-----------
Error_Line Error_Number Error_Severity Error_State
----------- ------------ -------------- -----------
4 8134 16 1
Error_Message
------------------------------------
Divide by zero error encountered.
Note
Any line number is this chapter is dependent on the amount of whitespace in the batch. Your results may vary.
The ERROR_NUMBER() function returns the error number that caused the code to jump into this CATCH block. This value will not change for the duration of the CATCH block. You can see a list of system messages and errors in sys.messages.
The ERROR_MESSAGE() function returns the text of the error that caused the code to jump into the CATCH block. For system errors this value can also be found in sys.messages. The ERROR_MESSAGE() function will return the message with any parameters expanded.
You can return the severity of the error using the ERROR_SEVERITY() function. TRY...CATCH blocks behave differently depending on the severity of the error. Errors (or messages) with a severity of 10 or less don’t cause the CATCH block to fire. These are typically informational messages. Error severities of 11 or higher will cause the CATCH block to fire unless the error terminates the connection. Error severities from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 usually indicate a software or hardware problem, where processing may be unable to continue. Books Online has a detailed description of each severity under the heading “Database Engine Error Severities.”
The ERROR_STATE() function can be used to determine the error state. Some system error messages can be raised at different points in the SQL Server engine. SQL Server uses the error state to differentiate when these errors are raised.
The last two properties of an error are the line number and the name of the stored procedure where the error occurred. These can be returned using the ERROR_LINE() function and the ERROR_PROCEDURE() function, respectively. The ERROR_PROCEDURE() function will return NULL if the error occurs outside a stored procedure. Listing 4 is an example of these last two functions inside a stored procedure.
Listing 4. ERROR_LINE and ERROR_PROCEDURE functions in a stored procedure
CREATE PROCEDURE ChildError
AS
BEGIN
RAISERROR('My Error', 11, 1)
END
GO
CREATE PROCEDURE ParentError
AS
BEGIN
EXEC ChildError
END
GO
BEGIN TRY
EXEC ParentError
END TRY
BEGIN CATCH
SELECT Error_Line = ERROR_LINE(),
Error_Proc = ERROR_PROCEDURE()
END CATCH
This returns the following result:
Error_Line Error_Proc
----------- -------------
4 ChildError
Let’s look now at how we can generate our own custom error messages.
Generate your own errors using RAISERROR
The RAISERROR function can be used to generate SQL Server errors and initiate any error processing. The basic use of RAISERROR for a dynamic error looks like this:
RAISERROR('Invalid Customer', 11, 1)
This returns the following when run in SQL Server Management Studio:
Msg 50000, Level 11, State 1, Line 1
Invalid Customer
The first parameter is the custom error message. The second is the severity (or level). Remember that 11 is the minimum severity that will cause a CATCH block to fire. The last parameter is the error state.
RAISERROR can also be used to return user-created error messages. The code in listing 5 illustrates this.
Listing 5. Returning user-created error messages with RAISERROR
EXEC sp_addmessage
@msgnum = 50001,
@severity = 11,
@msgtext = 'My custom error',
@replace = 'replace';
GO
RAISERROR(50001, 11, 1);
GO
This returns the following result:
Msg 50001, Level 11, State 1, Line 1
My custom error
The @REPLACE parameter of sp_addmessage says to replace the error if it already exists. When RAISERROR is called with a message description rather than an error number, it returns an error number 50000.
Ordinary users can specify RAISERROR with severity levels up to 18. To specify severity levels greater than 18, you must be in the sysadmin fixed server role or have been granted ALTER TRACE permissions. You must also use the WITH LOG option. This option logs the messages to the SQL Server log and the Windows Application Event Log. WITH LOG may be used with any severity level. Using a severity level of 20 or higher in a RAISERROR statement will cause the connection to close.
Nesting TRY...CATCH blocks
TRY...CATCH blocks can be nested inside either TRY or CATCH blocks. Nesting inside a TRY block looks like listing 6.
Listing 6. Nesting TRY...CATCH blocks
BEGIN TRY
PRINT 'One'
BEGIN TRY
PRINT 1/0
END TRY
BEGIN CATCH
PRINT 'Caught by the inner catch'
END CATCH
PRINT 'Two'
END TRY
BEGIN CATCH
PRINT 'Caught by the outer catch'
END CATCH
This batch returns the following result:
One
Caught by the inner catch
Two
This allows specific statements inside a larger TRY...CATCH to have their own error handling. A construct like this can be used to selectively handle certain errors and pass any other errors further up the chain. Here’s an example in listing 7
Listing 7. Error handling with nested TRY...CATCH statements
BEGIN TRY
PRINT 'One'
BEGIN TRY
PRINT CAST('Hello' AS DATETIME)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134
PRINT 'Divide by zero. Again.'
ELSE
BEGIN
DECLARE @ErrorNumber INT;
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE() + ' (%d)',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR( @ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber )
END
END CATCH
PRINT 'Two'
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE()
END CATCH
This returns the following result:
One
Error: Conversion failed when converting datetime from character string.
(241)
In the inner CATCH block I’m checking whether we generated error number 8134 (divide by zero) and if so, I print a message. For every other error message, I “reraise” or “rethrow” the error to the outer catch block. Note the text string that’s added to the error message variable. The %d is a placeholder that’s replaced by the first additional parameter passed to RAISERROR, which is @ErrorNumber in my example. Books Online has more information on the different types of replace variables that can be used in RAISERROR.
The error functions can be used in any stored procedure called from inside the CATCH block. This allows you to create standardized error-handling modules such as the one in listing 8.
Listing 8. An error-handling module
CREATE PROCEDURE ErrorHandler
AS
BEGIN
PRINT 'I should log this error:'
PRINT ERROR_MESSAGE()
END
GO
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
EXEC ErrorHandler
END CATCH
This block of code will return the following results:
I should log this error:
Divide by zero error encountered.
This is typically used to handle any errors in the code that logs the error information to a custom error table.
TRY...CATCH and transactions
A common use for a TRY...CATCH block is to handle transaction processing. A common pattern for this is shown in listing 9.
Listing 9. Transaction processing in a TRY...CATCH block
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.invoice_header
(invoice_number, client_number)
VALUES (2367, 19)
INSERT INTO dbo.invoice_detail
(invoice_number, line_number, part_number)
VALUES (2367, 1, 84367)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
-- And rethrow the error
END CATCH
Remember that the CATCH block completely consumes the error; therefore it is important to return some type of error or message back to the calling program.
Handling SQL Server errors on the client
The examples in this section use C# as the client application. Any .NET client application that supports try...catch constructs will behave in a similar fashion. The key points to learn here are which .NET classes are involved in error handling and what methods and properties they expose.
When a .NET application executes a SQL statement that causes an error, it throws a SqlException. This can be caught using a try...catch block like we saw previously. The SQL Server exception could also be caught by catching a plain Exception, but the SqlException class provides additional SQL Server–specific properties. A simple example of this in C# is shown in listing 10.
Listing 10. Outputting SQL Server–specific error properties with SqlException
using System.Data;
using System.Data.SqlClient;
class Program
{
SqlConnection conn = new SqlConnection("...");
SqlCommand cmd = new SqlCommand("RAISERROR('My Error', 11, 1)", conn);
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
Console.WriteLine("Error Message: " + sqlex.Message);
Console.WriteLine("Error Severity: {0}", sqlex.Class.ToString());
Console.WriteLine("Line Number: {0}", sqlex.LineNumber.ToString());
}
}
This returns the following result:
Error Message: My Error
Error Severity: 11
Line Number: 1
Exceptions with a severity of 10 or less don’t trigger the catch block on the client. The connection is closed if the severity level is 20 or higher; it normally remains open if the severity level is 19 or less. You can use RAISERROR to generate severities of 20 or higher and it’ll close the connection and fire the try...catch block on the client. The error returned typically indicates that the connection was closed rather than the error text you specified.
The SqlException class inherits from the System.SystemException and includes many properties that are specific to .NET. Some key SQL Server–specific properties of the SqlException class are shown in table 1.
Table 1. SQLException class properties
|
Property |
Description |
|---|---|
|
Class |
Error severity level |
|
LineNumber |
Line number in the batch or stored procedure where the error occurred |
|
Message |
Description of the error |
|
Number |
SQL Server error number |
|
Procedure |
Stored procedure name where the error occurred |
|
Server |
SQL Server instance that generated the error |
|
Source |
Provider that generated the error (for example, .Net SqlClient Data Provider) |
|
State |
SQL Server error state (the third parameter of RAISERROR) |
Another interesting property of the SqlException class is the Errors property. This is a collection of SqlError objects. The SqlError class includes only the SQL Server–specific properties from the SqlException object that are listed in table 1. Because a batch of SQL can generate multiple SQL Server errors, an application needs to check whether multiple errors have occurred. The first error in the Errors property will always match the error in the SqlExcpetion’s properties. Listing 11 is an example.
Listing 11. Handling multiple errors with the Errors property
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Server=L60YUKON; Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand(
@"RAISERROR('My Error', 11, 17)
SELECT 1/0
SELECT * FROM dbo.BadTable", conn);
try
{
cmd.Connection.Open();
cmd.ExecuteReader();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
for (int i = 0; i < sqlex.Errors.Count; i++)
{
Console.WriteLine("Error #{0}: {1}",
i.ToString(), sqlex.Errors[i].Message);
}
}
}
}
This returns the following result:
Error #0: My Error
Error #1: Divide by zero error encountered.
Error #2: Invalid object name 'dbo.BadTable'.
In closing, let’s look at how we can handle SQL Server messages inside our application code.
Handling SQL Server messages on the client
When a message is sent from SQL Server via a PRINT statement or a RAISERROR with a severity level of 10 or less, it generates an event on the .NET side. You can capture this event by writing a handler for the SqlConnection class’s InfoMessage event. The handler for the InfoMessage event takes two parameters: the sender and an instance of SqlInfoMessageEventArgs. This class contains three properties. The first is the Message that was printed or generated by the RAISERROR statement. The second is the Source, which is usually the .Net SqlClient Data Provider. The third is the Errors property, which is a collection of SqlError objects and behaves just like it did when we saw it earlier. Listing 12 is an example.
Listing 12. Outputting SQL Server messages
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Server=L60YUKON; Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("PRINT 'Hello'", conn);
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.CommandText ="RAISERROR('An error as message', 5, 12)";
cmd.ExecuteNonQuery();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
Console.WriteLine("First Error Message: " + sqlex.Message);
Console.WriteLine("Error Count: {0}", sqlex.Errors.Count.ToString());
}
}
static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("SQL Server Message: {0}", e.Message);
Console.WriteLine("Message Source: {0}", e.Source);
Console.WriteLine("Message Count: {0}", e.Errors.Count.ToString());
}
}
This returns the following result:
SQL Server Message: Hello
Message Source: .Net SqlClient Data Provider
Message Count: 1
SQL Server Message: An error as message
Message Source: .Net SqlClient Data Provider
Message Count: 1
No error returned
Another interesting characteristic of this approach is that you can capture informational RAISERROR statements as they’re executed rather than when a batch ends. Listing 13 shows an example.
Listing 13. Capturing RAISERROR statements
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Server=L60YUKON; Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand(
@"PRINT 'Printed at buffer flush'
RAISERROR('Starting', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:03';
RAISERROR('Status', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:03';
PRINT 'Done';", conn);
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_ShortMessage);
try
{
cmd.Connection.Open();
cmd.ExecuteReader();
Console.WriteLine("No error returned");
}
catch (SqlException sqlex)
{
Console.WriteLine("First Error Message: " + sqlex.Message);
Console.WriteLine("Error Count: {0}", sqlex.Errors.Count.ToString());
}
}
static void conn_ShortMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("[{0}] SQL Server Message: {1}",
System.DateTime.Now.ToLongTimeString(), e.Message);
}
}
This returns the following result:
[3:39:26 PM] SQL Server Message: Printed at buffer flush
[3:39:26 PM] SQL Server Message: Starting
[3:39:29 PM] SQL Server Message: Status
[3:39:32 PM] SQL Server Message: Done
No error returned
Normally, when you do a series of PRINT statements inside a SQL Server batch or stored procedure, the results are all returned at the end. A RAISERROR WITH NOWAIT is sent immediately to the client, as is any previous PRINT statement. If you remove the WITH NOWAIT from the first RAISERROR, the first three lines are all printed at the same time when the RAISERROR WITH NOWAIT pushes them all to the client. This approach can provide a convenient way to return status information for long-running tasks that contain multiple SQL statements.
Summary
SQL Server error handling doesn’t need to be an afterthought. SQL Server 2005 provides powerful tools that allow developers to selectively handle, capture, and consume errors inside SQL Server. Errors that can’t be handled on the server can be passed back to the application. .NET has specialized classes that allow applications to capture detailed information about SQL Server exceptions.
About the author
Bill Graziano has been a SQL Server consultant for 10 years, doing production support, performance tuning, and application development. He serves on the board of directors for the Professional Association for SQL Server (PASS), where he serves as the vice president of marketing and sits on the executive committee. He’s a regular speaker at conferences and user groups across the country. Bill runs the popular web site http://SQLTeam.com and is currently a SQL Server MVP.