Managing and Raising User-Defined Errors

As mentioned, SQL Server allows you to create customized error messages and raise them inside your code. As a database developer, you should create a set list of additional messages for use by your application so you can reuse them inside your programmable objects. Also, user-defined errors can significantly improve readability of operations and problems that occur in your application. Remember that these user-defined error messages help you customize and standardize messages returned from the procedures, functions, and other programmable objects in your database. This feature also provides you with an efficient way to develop code with the reuse of the same messages.

System Procedures and Parameters

User-defined error messages can be managed with the use of three system procedures: sp_addmessage, sp_altermessage, and sp_dropmessage. The sp_addmessage procedure lets you store a user-defined error message in an instance. Remember that these messages can be viewed using the sys.messages catalog view. The syntax of this procedure is as follows: