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