Systems Engineering and RDBMS

TRY…CATCH functionality in SQL Server 2005

Posted by decipherinfosys on September 17, 2007

In SQLServer 2005, Microsoft introduced new construct of TRY-CATCH block for effective error handling. This is similar to Oracle’s syntax of handling exceptions in PL/SQL by using BEGIN..EXCEPTION..END block. And this is very similar to the exception handling features of Microsoft Visual C++ and C# languages. Using TRY-CATCH block, we can make code more modular and can handle errors more precisely for a set of statements.

Here is a small code snippet which demonstrates how we can use this new functionality:

CREATE TABLE dbo.Test
( TEST_ID INT NOT NULL,
TEST_DESC VARCHAR(10) NOT NULL
)

We will now try to insert only the description without any ID value in order to simulate an error condition:

DECLARE @TEST_DESC VARCHAR(10)

BEGIN TRY
INSERT INTO TEST(TEST_DESC) VALUES(‘Hello’)
SELECT @Test_Desc = Test_Desc
FROM dbo.TEST
WHERE Test_ID = 1
PRINT ‘Description..’ + @Test_Desc
END TRY
BEGIN CATCH
/*SELECT 1/0*/
SELECT @@ERROR AS ErrorCode
SELECT
ERROR_NUMBER() AS ENumber,
ERROR_SEVERITY() AS ESeverity,
ERROR_STATE() as EState,
ERROR_PROCEDURE() as EProcedure,
ERROR_LINE() as ELine,
ERROR_MESSAGE() as EMessage

END CATCH
GO

If we execute the T-SQL block shown above, it will throw an exception message of cannot insert null value into TEST_ID column. Following is the formatted output.

ErrorCode
———–
515

ENumber ESeverity EState EProcedure ELine EMessage
——- ——— ——- ———- —– ————-
515 16 2 NULL 4 Cannot insert the value NULL
into column ‘TEST_ID’, table ‘DECIPHER.dbo.TEST’; column does not allow
nulls. INSERT fails.

Whenever the very first error is encountered in a TRY block, control immediately goes to CATCH block. No other statement in TRY block will be executed. Once CATCH block execution is complete, control will go to next statement immediately after CATCH block. If there are no errors in the TRY block, then also control will go to next statement immediately after CATCH block.

All the error functions mentioned in the CATCH block returns value only if they are used within the CATCH block otherwise outside of the block, they all return null values. We still can use standard syntax @@ERROR to get the message ID of the error but it should be the first statement in CATCH block. It may give wrong results if it is not the case.

Remove the comment from ‘SELECT 1/0’ statement and re-run the code. This time @@ERROR will return different result but ERROR() functions will display the same result as previously. This also implies that any error in the CATCH block can be handled if there is nested TRY-CATCH block within the CATCH block otherwise error will be thrown back to calling program.

Certain types of error are not handled by CATCH block. They are:
• Compilation errors.
• Object name resolution errors.
• Errors with severity 10 or lower since they are informational messages.
• Errors with severity 20 or higher which closes the database connection.
• If the error terminates the connection, it is not caught by the TRY-CATCH block.

Couple of other salient features for the TRY…CATCH functionality:
• TRY…CATCH blocks can be nested.
• If there is code that is written within the CATCH block, you will need to write a TRY…CATCH block within the specified CATCH block.
• TRY…CATCH can be used to handle deadlocks as well. We had covered that in one of our previous blog post here.
• RAISERROR can also be used within either the TRY or the CATCH block. If the error severity is between 11 and 19 and RAISERROR is being used in the TRY block, the control is transferred to the associated CATCH block. If RAISERROR is in the CATCH block, then an error is returned to the calling batch/application.
• TRY-CATCH cannot be used in a User Defined Function.

One Response to “TRY…CATCH functionality in SQL Server 2005”

  1. […] made after each statement.  If you are using SQL Server 2005 and above, you should really move to TRY…CATCH block […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: