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