TRY…CATCH construct is used for error handling in side T-SQL code. A TRY…CATCH construct consists of 2 blocks. One is TRY block that is immediately followed by a CATCH block. TRY…CATCH construct can be nested. When an error condition is identified in TRY block, the control is immediately transferred to CATCH block where the error can be handled.
The following code represents a basic TRY…CATCH construct.
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
END CATCH
Error Functions:
The following error functions can be used inside a TRY…CATCH construct to get the information about the error that is occurred.
ERROR_NUMBER() returns the error number.
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.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
TRY…CATCH with RAISERROR
RAISERROR can be used inside a TRY…CATCH construct for error handling. RAISERROR generates error message using user defined messages that is stored inside sys.messages catalog view or build a message dynamically.
The following code returns an error message and the WITH LOG statement causes the error to be logged into windows application log. Using event viewer, you can view the log.
BEGIN TRY
RAISERROR ('Error raised in TRY block.', 16,10) WITH LOG;
END TRY
BEGIN CATCH
SELECT Error_Message() as Message_text,
Error_Severity() as Severity,
Error_State() as State
END CATCH
Questions:
1. What types of error cannot be handled by TRY…CATCH construct. Choose all correct answers from the following:
a. All DDL statement errors
b. All DML statement errors
c. Divide by Zero error
d. Syntax errors that prevent a batch from executing.
Answers:
1. d.
