Error handling in Transact SQL
Error handling is a fundamental aspect in managing Transact SQL code, which allows us to control our code flow and detect problems and errors making it easier for us to resolve them.
Through the manage of exceptions, we can have full control over our application and detect what causes interruptions in our code.
There are flow control structures specially designed for Error handling. In this section we will focus on
TRY...CATCH is a flow control structure that allows us to detect and catch errors. The essential operation is simple:
-TRY: encloses a series of Transact SQL statements that will attempt to execute.
- CATCH: collects any error that occurs in the
TRY, executing the statements of this
CATCHblock, which are usually intended to provide information about the error produced.
Ultimately, if everything goes well, the code belonging to the
CATCH clause is not executed. However, if an error occurs during the execution of the
TRY, the code will jump to the
CATCH to catch it and deal with it.
Regarding the syntax, it is the following:
BEGIN TRY [code] END TRY BEGIN CATCH [code] END CATCH
There are a few things to keep in mind about
It can be used inside stored procedures.
TRY...CATCHstatements can be nested.
TRYmust always be followed immediately by its corresponding
CATCHblock. Any statement between
BEGIN CATCHwill cause a syntax error.
It does not allow to encompass several lots. For example, it cannot contain more than one
There are a series of functions in charge of returning information about the errors that have occurred, which are often used within the
ERROR_NUMBER(): returns the error number.
ERROR_SEVERITY(): returns the severity of the error.
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 within the routine that caused the error.
ERROR_MESSAGE(): returns the full text of the error message.
To exemplify how
TRY...CATCH works in a simple way, we proceed to try dividing a number by zero within a
TRY...CATCH block to capture and return error information:
BEGIN TRY SELECT 1/0; 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; END CATCH; GO
|8134||16||1||NULL||2||Divide by zero error encountered.|
THROW allows us to generate our own exceptions and return them in the
CATCH block of a
TRY...CATCH construct. This way we can handle a custom **error handling**.
The syntax is:
THROW (error_number | variable, message | variable , state | variable )
As we can see from its syntax, the
THROW statement uses three main arguments in its declaration:
error_number: Indicates the number of the error. It will be
INTdata type and its value must be between 50000 and 2147483647 (both included).
message: returns the full text of the error message that we specify.
state: returns the error status number. It will be of type
TINYINTwith a value between 0 and 255 (both included).
To exemplify how
THROW works in a simple way, we proceed to try to divide a number by zero within a
TRY...CATCH block to capture and return the custom information of the generated error:
BEGIN TRY SELECT 1/0; END TRY BEGIN CATCH THROW 50100, 'The mathematical operation is wrong.',1; END CATCH; GO
Msg 50100, Level 16, State 1, Line 5 The mathematical operation is wrong.