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