Generating Errors from Stored Procedures

Transact-SQL (T-SQL) provides a RAISERROR (note the spelling) function, which you can use to generate custom errors and return them to the client. For ADO.NET clients, the SQL Server .NET Data Provider intercepts these database errors and translates them to SqlError objects.
The simplest way to use the RAISERROR function is to include the message text as the first parameter, and then specify severity and state parameters, as shown in the following code fragment.

RAISERROR( 'Unknown Product ID: %s', 16, 1, @ProductID )

In this example, a substitution parameter is used to return the current product ID as part of the error message text. Parameter two is the message severity, and parameter three is the message state.

More Information

To avoid hard coding message text, you can add your own message to the sysmessages table by using the sp_addmessage system stored procedure, or by using the SQL Server Enterprise Manager. You can then reference the message by using an ID passed to the RAISERROR function. The message IDs that you define must be greater than 50,000, as shown in the following code fragment.

·         RAISERROR( 50001, 16, 1, @ProductID )

For full details relating to the RAISERROR function, look up RAISERROR in the SQL Server Books Online index.

Using Severity Levels Appropriately

Choose your error severity levels carefully and be aware of the impact of each level. Error severity levels range from 0 to 25 and are used to indicate the type of problem that SQL Server 2000 has encountered. In client code, you can obtain an error's severity by examining the Class property of the SqlError object, within the Errors collection of the SqlException class. Table 1 indicates the impact and meaning of the various severity levels.

Table 1. Error Severity Levels–Impact and Meaning

Severity level	Connection is closed	Generates SqlException	Meaning
10 and below	No	No	Informational messages that do not necessarily represent error conditions.
11–16	No	Yes	Errors that can be corrected by the user—for example, by retrying the operation with amended input data.
17–19	No	Yes	Resource or system errors.
20–25	Yes	Yes	Fatal system errors (including hardware errors). Client's connection is terminated.

Controlling Automatic Transactions

The SQL Server .NET Data Provider throws a SqlException for any error encountered with a severity greater than 10. When a component that is part of an automatic (COM+) transaction detects a SqlException, the component must ensure that it votes to abort the transaction. This might or might not be an automatic process, and depends on whether or not the method is marked with the AutoComplete attribute.

Shashi Ray