Practice handling errors in your Transact-SQL code.
Handling Errors SQL- Server
Transact-SQL Code | Raising Errors
In the previous lesson, you learned about system errors, which are generated automatically by SQL Server.
In some cases, you will want to generate an error and return it back to a calling program. For example, stored procedures, which you will learn about later in this course, often require you to return an error back to the program that called the stored procedure.
These types of errors are called user-defined errors. There are two types of user-defined errors:
User-defined static error: A user-defined message that exists in the sysmessages system catalog. These errors can be raised by your Transact-SQL statements. User-defined messages are added to the sysmessages system catalog by using the sp_addmessage system stored procedure.
User-defined dynamic error: A user-defined message whose DESCRIPTION is generated dynamically and does not reside in the
sysmessages system catalog. This type of error can be raised by your Transact-SQL statement.
User-defined static error
To raise an error, use the RAISERROR function, as shown below:
The following Transact-SQL statement is used to raise a dynamic error with a severity level of 16 and a state of 1:
RAISERROR ('An error occurred', 16, 1)
This Transact-SQL statement raises a fatal error and writes it to the application error log:
RAISERROR ('An error occurred', 20, 1) WITH LOG
This Transact-SQL statement raises error number 50001, which must exist in the sysmessages system catalog:
RAISERROR (50001, 16, 1)
In the next lesson, the information covered in this module will be reviewed.