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

Get ADO.NET Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.