[ Team LiB ] Recipe 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 event handler to catch informational and warning messages. | Team LiB Recipe 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 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 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. 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 Namespaces variables and constants using System using using using . . . private void raiseErrorButton_Click object sender e Severity State Create the connection. SqlConnection conn new SqlConnection S ql_ConnectString Attach handler for SqlInfoMessage events. new .
