Handling Errors in T-SQL
Introduction
The Transact-SQL language has several error-handling capabilities. In this guide, you will learn the fundamentals of T-SQL error handling, raising errors intentionally, and how to raise alerts when errors occur.
In SQL Server, errors can be generated either by the SQL Server engine when an error occurs or by custom T-SQL code.
Types of Errors
There are two types of errors in SQL Server: system errors and custom errors. System errors can be viewed in the sys.messages system view and are defined by SQL server. Therefore, when a system error occurs, SQL Server will log a system error and may take actions to fix the error.
Custom errors, on the other hand, are generated by T-SQL custom codes based on your code or business logic. To add a custom error message to sys.messages, the stored procedure sp_addmessage is used.
Below is an example of adding a custom error message:
1EXEC sp_addmessage 50001, 16,
2N'Unit price needs to be greater than 0'.
3GO In the example above, 50001 is the message id. This parameter can be an integer between 50,001 and 2,147,483,647.
16 is the severity, which is smallint and ranges from 1 through 25.
Raising Errors with RAISERROR
RAISERROR allows applications to generate an error that could then be caught by the calling process. This makes error handling in the application easier as it is sent like any other system error. RAISERROR can therefore be used to troubleshoot T-SQL codes, debug and check the value of variables and return meaningful error messages based on variables data.
Below is an example of using RAISERROR in SQL Server:
1RAISERROR (N'This is message.', -- Message text.
210, -- Severity,
31); -- Second argument.
4GO The output would then be as follows:
This is message.
Raising Errors with THROW
The THROW statement is a simpler method of raising errors in T-SQL code.
Below is an example of how THROW is used in SQL Server:
1THROW
251000, -- error number
3'This is not a valid value for unit price.', -- message
41; --state
5GO The result set would then be as follows:
1Msg 51000, Level 16, State 1, Line 1
2This is not a valid value for unit price.In the example above, 51000 is the error number. The error number is an integer that must be a value between 50000 and 2147483647. The next parameter is the message, which is a string containing a description of the error. It's format is nvarchar(2048), and finally we have the state, which is a constant between 0 and 255. It shows the state to associate with the message. State is of type tinyint.
Understanding the @@Error variable
@@ERROR is a system variable that holds the error number of the last error that has occurred. One of the drawbacks of using @@ERROR is that the value it holds resets as each additional statement is executed. To get the last error number, the query below is used:
1Select @@ERRORExample of Using @@Error
Consider the example below where a custom error is raised before selecting the value of @@ERROR.
1RAISERROR(N'Message', 16, 1);
2IF @@ERROR <> 0
3PRINT 'Error=' + CAST(@@ERROR AS VARCHAR(8));
4GOThe output is as follows:
1Msg 50000, Level 16, State 1, Line 1
2Message
3Error=0Therefore, when working with @@ERROR, it is recommended to capture the error number into a variable as soon as it occurs and then continue processing with the variable. This is demonstrated in the example below.
1DECLARE @Value int;
2RAISERROR(N'Message', 16, 1);
3SET @Value = @@ERROR;
4IF @Value <> 0
5PRINT 'Error=' + CAST(@Value AS VARCHAR(8));The output will then be as below, where the ID of the message is successfully captured in the variable @Value.
1Msg 50000, Level 16, State 1, Line 2
2Message
3Error=50000Error Handling in T-SQL
The T-SQL Try Catch is used to handle errors in SQL Server programming. TRY-CATCH in SQL Server works in a similar manner as exception handling in popular programming languages such as Java and C#. For example, when a T-SQL code is written using TRY CATCH and a code in the TRY blocks fails, the execution flow will exit the TRY block and move to the CATCH block.
Below is an example of error handling in SQL Server:
1BEGIN TRY
2SELECT 50/0
3END TRY
4BEGIN CATCH
5SELECT ERROR_NUMBER() AS ErrorNumber
6,ERROR_STATE() AS ErrorState
7,ERROR_LINE() AS ErrorLine
8,ERROR_MESSAGE() AS ErrorMessage;
9END CATCHIf we executed only 50/0, the execution would fail with no way to handle the error. In the example above, when the error occurred, the flow moved to the catch block where the error is handled. For example, in the catch block, we could have logged the error in a log table to keep track of the error.
Also, note the use of the functions ERROR_NUMBER(), ERROR_STATE(), ERROR_LINE() and ERROR_MESSAGE(), which are very helpful in the catch block.
Conclusion
In this guide you have learned the basics of error handling in T-SQL. If done properly, error handling gives you not only the possibility to better understand errors in your database, but also the opportunity to log the errors as they happen.
To learn more about T-SQL, you can also read the following guides:
Happy Coding!