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. Show
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 ErrorsThere 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:
sql 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 RAISERRORRAISERROR 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:
sql The output would then be as follows: This is message. Raising Errors with THROWThe 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:
sql The result set would then be as follows:
sql 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:
sql Example of Using @@ErrorConsider the example below where a custom error is raised before selecting the value of @@ERROR.
sql The output is as follows:
sql Therefore, 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.
sql The output will then be as below, where the ID of the message is successfully captured in the variable @Value.
sql Error Handling in T-SQLThe 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:
sql If 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. ConclusionIn 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. What is the use of @@ error in SQL Server?The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.
What is the maximum amount of @@ Nestlevel?When the maximum of 32 is exceeded, the transaction is terminated.
How to display error message in stored procedure in SQL Server?ERROR_STATE() returns the error state number. ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred. ERROR_LINE() returns the line number inside the routine that caused the error. ERROR_MESSAGE() returns the complete text of the error message.
What is error number in SQL Server?When called in a CATCH block, ERROR_NUMBER returns the error number of the error that caused the CATCH block to run. ERROR_NUMBER returns NULL when called outside the scope of a CATCH block.
|