Recipe 2.10 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.