SQL SERVER – TSQL – Handling Errors

For SQL Server 2000 and earlier version, the most common way to working error as using @@ERROR function. SQL Server 2005 introduces the construct TRY…CATCH, it improves handling errors. The construct TRY…CATCH is the principal tool for handling error on SQL Server, specifically with exceptions errors. This capability allows you to manage exception errors in similar way as .NET programming languages.

A TRY…CATCH consist in two parts: these are a TRY block followed immediately by a CATCH block. All the TSQL Code must to be on TRY block and the handling error must to be on CATCH block. When in the TRY block does not exist error, the CATCH block is simply skipped. If the TRY block has an error, then the control is transferred to the next CATCH block.

Both, TRY and CATCH block starts with BEGIN TRY and BEGIN CATCH respectively, and ends with END TRY and END CATCH respectively.

You can test the following code in order to see a case without error on TRY block.

-- TSQL WORKING FINE

BEGIN TRY

PRINT 100 / 10

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The code on TRY block was executed successfully; that’s way the CATCH block was not executed. The result is the following:

10

Working on BLOCK TRY. WITHOUT ERRORS!!!!!!

Now, execute the following code with error on the TRY block:

-- TSQL WITH ERROR - DIVIDE BY ZERO

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The divide by zero causes an ERROR on the TRY block, it cause that the control was passed immediately to CATCH BLOCK and also cause that the second PRINT on TRY block was not executed. The result is the following.

Working on BLOCK CATCH WITH ERRORS!!!!!!

To obtain information about the error that cause the CATCH block to be executed, SQL Server provides the following system functions:

ERROR_NUMBER() function returns an integer with the number of error

ERROR_SEVERITY() function returns the severity number.

ERROR_STATE() function returns the error state number.

ERROR_LINE() function returns the line number inside the routine that caused the error.

ERROR_MESSAGE() function 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_PROCEDURE() function returns the name of the stored procedure or trigger where the error occurred.

You can execute the following code in order to create a Stored Procedure that encapsulates the system functions mentioned above. Also you can customize the error messages. The following code is customized when the error number 208 (Invalid Object Name) happens, this customized message error will be used later. :

create procedure dbo.sp_err_msg

as

if ERROR_NUMBER() = 208

print 'Oops, the table queried does not exists. '

else

select

ERROR_NUMBER() as ErrorNumber,

ERROR_SEVERITY() as Severity,

ERROR_STATE() as State,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

ERROR_PROCEDURE() as ErrorOnProcedure

GO

You can modify the second code used in this article in order to see how works the error system functions.

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The divide by zero cause an error on TRY block, then the control was passed to CATCH block and the stored procedure is executed. The result is the following.

Some errors are not handled by construct TRY…CATCH :

• Compilation errors. Ex syntax error.
• Recompilation errors. Ex object name resolution error.

If an compilation or recompilation errors occurs when they are at TRY … CATCH level, those errors are not handled by construct TRY …CATCH. On the other hand, if the error occurs at a lower execution level (ex. Sp_executesql or a stored procedure) the errors are handled by the construct TRY…CATCH.

Run the following code to demonstrate an event that is not handle by TRY…CATCH. The code, execute a SELECT statement to a non existing table, it cause an error:

BEGIN TRY

SELECT * FROM TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

Result:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'TABLENOTDEFINED'.

However, if the SELECT statement is executed by a stored procedure the error will occur at a level lower than TRY block. Then, the error will be handled by the construct TRY … CATCH.

create procedure dbo.sp_exec_TABLENOTDEFINED

as

SELECT * FROM TABLENOTDEFINED

GO

BEGIN TRY

exec dbo.sp_exec_TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The table non defined on the Stored procedure cause an error 208, it is handled by TRY ... CATCH, and a customized error message is shown.

Result.

Oops, the table queried does not exists.

Conclusion

TRY..CATCH is the main tool provided by SQL Server to handling errors. To obtain more information about the errors is possible to use a system functions as: ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE().