ADO.NET 3.5 Cookbook, 2nd Edition

3.24. 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 example uses a single stored procedure, shown in Example 3-34:

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.

Example 3-34. Stored procedure: RaiseError

CREATE PROCEDURE RaiseError
    @Severity int,
    @State int = 1
AS
    IF @Severity <=18
        RAISERROR ('Error of severity %d raised from stored procedure RaiseError.',
            @Severity, @State, @Severity)
    ELSE
        RAISERROR ('Error of severity %d raised from stored procedure RaiseError.',
            @Severity, @State, @Severity) WITH LOG

    RETURN

The solution creates a connection and attaches a handler named SqlMessageEventHandler() for warning and information messages from the SQL Server. A Command is created for the RaiseError stored procedure and the input parameters are defined. The stored procedure is called for error severity levels from -1 through 26 and results are output to the console demonstrating how errors of different severities are handled.

The C# code in Program.cs in the project RaiseAndHandleStoredProcedureError is shown in Example 3-35.

Example 3-35. File: Program.cs for RaiseAndHandleStoredProcedureError solution

using System; using System.Data; using System.Data.SqlClient; ...