Coding a TRY/CATCH block in T-SQL is similar to the TRY/CATCH blocks of other languages. The TRY/CATCH block consists of a block of code identified as a TRY block, followed by another block of code known as a CATCH block. If an error is encountered in the TRY block, then the CATCH block is executed to determine what actions should be taken to deal with the error encountered. Keep in mind that only errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block. Here is the syntax for a TRY/CATCH block:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH
The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block, otherwise you will receive a syntax error. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.
BEGIN TRY
DECLARE @X INT
-- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'
When I run this code on my server I get the following output:
Error Detected
Command after TRY/CATCH blocks
In the code above, the SET statement tries to divide by zero, which is an error. Because the SET statement received an error, the PRINT statement that immediately follows the SET statement is not executed. Therefore, this skipped PRINT statement does not display a message in the generated output. Instead, the first statement within the CATCH block, a PRINT statement, displays the message “Error Detected”.
If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. To see this in action take the code above and remove, or comment out the SET statement that does the divide by 0, and then execute. When you do this, you will see that the PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block.
Begin TRY
delete from GrandParent where Name = 'John Smith'
print 'GrandParent deleted successfully'
End Try
Begin Catch
Print 'Error Deleting GrandParent Record'
Begin Try
delete from Parent where GrandParentID =
(select distinct ID from GrandParent where Name = 'John Smith')
Print 'Parent Deleted Successfully'
End Try
Begin Catch
print 'Error Deleting Parent'
Begin Try
delete from child where ParentId =
(select distinct ID from Parent where GrandParentID =
(select distinct ID from GrandParent where Name = 'John Smith'))
print 'Child Deleted Successfully'
End Try
Begin Catch
Print 'Error Deleting Child'
End Catch
End Catch
End Catch
This code first tries to delete the grandparent record. If the delete of the grandparent record is unsuccessful then it tries to delete the parent record. If the parent record cannot be deleted then the child record is deleted. Each delete command is supported by a different, nested TRY/CATCH block.
ERROR_NUMBER(): Returns a number associated with the error. ERROR_SEVERITY(): Returns the severity of the error. ERROR_STATE(): Returns the error state number associated with the error. ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger in which the error occurred. ERROR_LINE(): Returns the line number inside the failing routine that caused the error. ERROR_MESSAGE(): Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.These error functions can only be used within the scope of a CATCH block. If these functions are used outside a CATCH block, they will return a NULL value. Let me demonstrate one way you can use these functions to return error information. Below is a slightly modified TRY/CATCH block of code, similar to my divide by zero example above:
BEGIN TRY
DECLARE @X INT
-- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
SELECT ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() Error_Severity,
ERROR_STATE() Error_State,
ERROR_PROCEDURE() Error_Procedure,
ERROR_LINE() Error_Line,
ERROR_MESSAGE() Error_Message
END CATCH
PRINT 'Command after TRY/CATCH blocks'
In this example, I added a SELECT statement to my CATCH block. This added SELECT statement displays the results returned for each of these available system error functions. When I execute this code on my server, I get results similar to the following:
Error Detected Err_Num Err_Sev Err_State Err_Proc Err_Line Err_Msg ------- ------- --------- -------------------- --------- -------------------------------- 8134 16 1 NULL 4 Divide by zero error encountered.
| You must Sign In to use this message board. | ||||||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
||||||||||||||||||||||||||
