SQL Server Error Handling and TRY CATCH
Mục lục
Introduce
Error handling in SQL Server is now easier through various techniques.
SQL Server has introduced options to help users control errors more effectively
Usually, the end user won’t be able to catch the error appearing on the application side.
Therefore, SQL Server provides a TRY … CATCH statement for more efficient error control at the backend.
Some system functions can print out relevant information about the error, making it easier to fix the error.
Types of errors
T-SQL programmers will have to be aware of the types of errors that occur when working with SQL Server statements. The first step is to identify the type of error, then figure out how to handle the error.
List of error types
Syntax Errors
Syntax error is encountered when the code is syntactically incorrect, SQL Server cannot understand. These errors are detected by SQL Server before starting the execution of the T-SQL block or stored procedure.
When the user types the wrong keywords or operations because he doesn’t remember the valid syntax, the code editor will point it out.
If the user forgets to type a keyword or an action is required to complete the command, the code editor will report an error when the user executes the command.
Syntax Errors (syntax errors) will be easily identifiable as the code editor will indicate, however, if using a command line application, the error will only show up after the user executes the command.
Run-time Errors
A runtime error occurs when an application tries to process a task that is not supported by SQL Server or the operating system.
Some run-time errors that can be encountered are as follows:
- Handling the calculation of division by 0
- Attempts to execute code that is not explicitly defined. (The example below has a situation where the value is larger than the data type can represent).
Common situations where run-time errors are encountered:
- Use stored procedure, or function, or trigger that is not available (deleted).
- Attempt to process a task that operates on an object or variable that cannot be processed (table deleted, not in database…)
- The situation of Ram overflow (stack overflow) when the application is overloaded
- Try to handle tasks with incompatible data types.
- Misuse of conditional statements.
Implement error handling
In the software development process, one of the important things is to pay attention to error handling. In some ways, users have to take care of handling exceptions when designing the database. Different processing mechanisms can be used.
- When executing DML statements like INSERT,DELETE,UPDATe, users can handle errors to ensure correct output.
- When the transaction fails and the user has to roll back the transaction, an appropriate error message should be displayed to the user.
- When working with cursors in SQL Server, users can handle errors to ensure accurate results.
TRY … CATCH command block
The TRY … CATCH block is used to implement exception handling in Transact SQL. One or more T-SQl instructions are bounded by a TR block. If an error occurs in a TRY block, control flows to a catch block, which may contain one or more statements within the CATCH block.
Syntax:
BEGIN TRY {sql_statement | statement_block} END TRY BEGIN CATCH [ {sql_statement | statement_block}] END CATCH [;]For example:
BEGIN TRY DECLARE @num int; SELECT @num=217/0; END TRY BEGIN CATCH PRINT 'Error occurred, unable to devide by 0' END CATCH;Error information
The best solution is to display the error information along with the error, so that it can help resolve the error quickly and efficiently.
To do this, system functions need to be used inside the CATCH block to find information about the error generated when the block is executed.
List of system functions that provide error information:
- ERROR_NUMBER() : returns the error number.
- ERROR_SERVERITY() : return severity
- ERROR_STATE(): returns the error status number.
- ERROR_PROCEDURE(): Returns the name of the trigger or stored procedure that caused the error.
- ERROR_LINE(): returns the number of lines that caused the error
- ERROR_MESSAGE(): returns the complete text of the error, the text will contain the values provided as parameters such as object names, length.
These functions will return NULL if called outside the CATCH block
Use TRY…CATCH with error information
USE AdventureWorks2019; GO BEGIN TRY SELECT 217/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumer, ERROR_SEVERITY() AS ErrorSeverity, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH; GOExample stored procedure contains error handling functions:
USE AdventureWorks2019; GO IF OBJECT_ID ('sp_ErrorInfo','P') IS NOT NULL DROP PROCEDURE sp_ErrorInfo; GO CREATE PROCEDURE sp_ErrorInfo AS SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; GO BEGIN TRY SELECT 217/0 END TRY BEGIN CATCH EXEC sp_ErrorInfo; END CATCHUsing TRY…CATCH in Transaction
USE AdventureWorks2019; GO BEGIN TRANSACTION; BEGIN TRY DELETE FROM Production.Product WHERE ProductID = 980; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GOUncommitable transaction
If an error is generated from the TRY block, it causes it to take effect, and the transaction is treated as an uncommitted transaction. An uncomitable transaction only performs a ROLLBACK TRANSACTION or read operation.
Transaction will not execute any Transact-SQL statements that perform transaction commits or write operations.
@@ERROR
The @@ERROR function returns the error number of the last T-SQL statement executed.
Syntax:
@@ERRORFor example:
USE AdventureWorks2019; GO BEGIN TRY UPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4 WHERE BusinessEntityID = 1; END TRY BEGIN CATCH IF @@ERROR = 547 PRINT N'Check constraint violation has occurred.'; END CATCHRAISERROR
The RAISERROR statement initiates error handling for a session and displays the error message.
RAISERROR can refer to user-defined messages stored in the sys.message catalog view or construct a dynamic error message at run-time.
Syntax:
RAISERROR ( {msg_id | msg_str | @local_variable} {,serverity , state} [,argument,[,...n]]) [WITH option [,...n]]In there:
- msg_id : Specifies user-defined error messages stored in the sys.messages catalog view using sp_addmessage.
- msg_str : defines user-defined error messages with formatting. msg_str is a string of characters with conversion options. Parameters can be d,i,o,s,x,X,u similar to string format in printf function.
List of values for custom options:
| Value | description |
| LOG | Log errors in database and application error logs. |
| NOWAIT | Send notifications directly to the client |
| SETERROR | Assign ERROR_NUMBER and @@ERROR value to msg_id or 5000 regardless of severity |
The following errors will be returned if RAISERROR is executed:
- Outside the scope of the TRY block
- Has a severity of 10 or less in the TRY block
- Severity 20 or higher, database disconnection.
Example using RAISERROR() to print out a custom error:
RAISERROR (N'This is an error message %s %d',10,1,N'serial number',23); GOExample using RAISERROR in TRY CATCH:
BEGIN TRY RAISERROR('Raises Error in the TRY block',16,1); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE() , @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState); END CATCH;ERROR_SEVERITY
The ERROR_SEVERITY function returns the severity of the error in the catch block of the TRY CATCH structure.
This function will return null if called outside of the CATCH block. ERROR _SEVERITY can be called anywhere within the scope of the CATCH block.
In a nested CATCH block, ERROR_SEVERITY will return an error determining the scope of the catch block it refers to.
Syntax:
ERROR_SEVERITY()For example:
BEGIN TRY SELECT 217/0; END TRY BEGIN CATCH SELECT ERROR_SEVERITY() AS ErrorSeverity; END CATCH; GOERROR_PROCEDURE
The ERROR_PROCEDURE function returns the name of the trigger or stored procedure where the error of a TRY CATCH construct was executed.
Syntax:
ERROR_PROCEDURE()It returns nvarchar data type. When the function is called in the CATCH block, it returns the name of the stored procedure where the error was encountered.
ERROR_PROCEDURE can be called anywhere within the CATCH block.
For example:
USE AdventureWorks2019; GO IF OBJECT_ID ('usp_Example','P') IS NOT NULL DROP PROCEDURE usp_Example; GO CREATE PROCEDURE usp_Example AS SELECT 217/0; GO BEGIN TRY EXECUTE usp_Example; END TRY BEGIN CATCH SELECT ERROR_PROCEDURE() AS ErrorProcedure; END CATCH; GOERROR_NUMBER
The ERROR_NUMBER function called in the CATCH block returns the number of errors generated by the CATCH block when the TRY >> CATCH structure is executed.
Syntax:
ERROR_NUMBER()ERROR_NUMBER returns the corresponding error code regardless of the number of error executions (in case of multiple exceptions in different statements) and it executes within the scope of the CATCH block. This is different from @@ERROR because it only returns the internal error number immediately upon an error, and the scope is only on the first statement in the CATCH block.
For example:
BEGIN TRY SELECT 217/0; SELECT 218/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH; GOERROR_MESSAGE
The ERROR_MESSAGE function returns the text message of the error caused in the structure in the TRY CATCH structure.
Syntax:
ERROR_LINE()When this function is called in a CATCH block, it returns the full text of the error message that caused the CATCH block to be executed.
The text includes the values provided for any parameter that can be substituted such as object name, time, or length.
For example:
BEGIN TRY SELECT 200/0; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; GOERROR_LINE
The ERROR_LINE function returns the number of lines with errors in the TRY CATCH block.
Syntax:
ERROR_LINE()When the function is called in the CATCH block, it returns the number of lines that encountered the error. If an error is encountered inside a trigger or stored procedure, it returns the number of lines in that trigger or stored procedure. Similar to other functions, this function returns NULL if it is called outside the scope of the CATCH block.
For example:
BEGIN TRY SELECT 217/0; END TRY BEGIN CATCH SELECT ERROR_LINE() AS ErrorLine; END CATCH; GOErrors that are not affected by the TRY CATCH construct
The TRY CATCH block will not catch the following conditions:
- The information of the message or alert has severity 10 or less.
- An error of severity 20 or higher stops the SQL Server Database Engine execution task for the session.
- If the error encountered has a severity of 20 or higher and the database connection is not interrupted, TRY CATCH will handle the error.
- Notice such as a broken client connection or interrupted requests.
- When the session ends with the KILL statement, via the system admin.
The following error types that are not handled by a CATCH block occur at the same execution level as that of the TRY CATCH construct:
- Compilation error as legal error.
- Errors that arise during statement-level compilation, such as object name resolution errors (object name does not exist) occur after compilation due to delayed name resolution.
For example:
USE AdventureWorks2109; GO BEGIN TRY SELECT * FROM Nonexistent; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCHTHROW
The THROW statement raises an exception and transfers control of execution to a CATCH block of a TRY…CATCH structure.
Syntax:
THROW [{error_number | @local_varibale}, {message | @local_variable}, {state | @local_variable}] [;]For example:
USE tempdb; GO CREATE TABLE dbo.TestRethrow (ID INT PRIMARY KEY); BEGIN TRY INSERT dbo.TestRethrow(ID) VALUES(1); INSERT dbo.TestRethrow(ID) VALUES(1); END TRY BEGIN CATCH PRINT 'In catch block.'; THROW END CATCH;When using THROW, even though the command fails to bind, the PRINT statement inside the CATCH block is still printed, which is the role of THROW in SQL Server.
