Raising and Handling Stored Procedure Errors

Problem

You want to catch and handle an error raised from a stored procedure.

Solution

Use a try . . . catch block to catch serious errors. Use the SqlConnection.InfoMessage event handler to catch informational and warning messages.

The sample code, as shown in Example 2-11, uses a single stored procedure and two event handlers:

SP0210_Raiserror

Accepts two input parameters specifying the severity and the state of an error and raises an error with the specified severity and state.

Raise Error Button.Click

Creates a connection and attaches a handler for warning and information messages from the SQL Server. A Command is created for the SP0210_Raiserror stored procedure and the input parameters are defined. The user -specified severity and state are assigned to the input parameters and the stored procedure command is executed within a try statement.

SqlConnection.InfoMessage

Called when a warning or informational message is raised by the SQL Server.

Example 2-11. Stored procedure: SP0210_Raiserror

CREATE PROCEDURE SP0210_Raiserror
 @Severity int,
 @State int = 1
AS
 if @Severity>=0 and @Severity <=18
 RAISERROR ('Error of severity %d raised from SP 0210_Raiserror.', @Severity, 
 @State, @Severity)
 
 if @Severity>=19 and @Severity<=25
 RAISERROR ('Fatal error of severity %d raised from SP 0210_Raiserror.',
 @Severity, @State, @Severity) WITH LOG
 
 RETURN

The C# code is shown in Example 2-12.

Example 2-12. File: RaiserrorForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// . . . 

private void raiseErrorButton_Click(object sender, System.EventArgs e)
{
 resultTextBox.Text =
 "Severity: " + severityTextBox.Text + Environment.NewLine +
 "State: " + stateTextBox.Text + Environment.NewLine +
 Environment.NewLine;

 // Create the connection.
 SqlConnection conn = new SqlConnection(
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 // Attach handler for SqlInfoMessage events.
 conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
 
 // Define a stored procedure command and the parameters.
 SqlCommand cmd = new SqlCommand("SP0210_Raiserror", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@Severity", SqlDbType.Int);
 cmd.Parameters.Add("@State", SqlDbType.Int);
 // Set the value for the stored procedure parameters.
 cmd.Parameters["@Severity"].Value = severityTextBox.Text;
 cmd.Parameters["@State"].Value = stateTextBox.Text;

 // Open the connection.
 conn.Open( );
 try
 {
 // Try to execute the stored procedure.
 cmd.ExecuteNonQuery( );
 }
 catch(System.Data.SqlClient.SqlException ex)
 {
 // Catch SqlException errors.
 resultTextBox.Text += "ERROR: " + ex.Message;
 }
 catch(Exception ex)
 {
 // Catch other errors.
 resultTextBox.Text += "OTHER ERROR: " + ex.Message;
 }
 finally
 {
 // Close the connection.
 conn.Close( );
 }
}

private void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
 resultTextBox.Text += "MESSAGE: " + e.Message;
}

Discussion

Errors and messages are returned from a SQL Server stored procedure to a calling application using the RAISERROR (note the spelling) function. The error message severity levels are listed in Table 2-12.

Table 2-12. RAISERROR error message severity levels

Severity level	Description
0-10	Informational
11-16	Error which can be corrected by the user
17-19	Resource or system error
20-25	Fatal error indicating a system problem

Severity levels greater than 20 result in the connection being closed.

Since severity levels 10 or less are considered to be informational, they raise a SqlInfoMessageEvent rather than an error. This is handled by subscribing a SqlInfoMessageEventHandler to the InfoMessage event of the SqlConnection object.

If the error has severity level 11 or greater, a SqlException is thrown by the SQL Server .NET data provider.

For more information about the RAISERROR function, look up RAISERROR in SQL Server Books Online.