SQL Server Exception handling

Error Handling is an important feature for any programming language. It helps developer to handle error inside the application code instead surprising user with debugging error messages.

SQL Server Error handling

SQL Server does have efficient and simplest error handling mechanism to handle T-SQL errors.

Try….Catch block

Try block

Developers can write the code inside TRY block. This code block will be executed normally as other statements. But, if any error occurs then it will throw that error to catch block instead to application.

Syntax

BEGIN TRY

<Statements>

END TRY

Catch block

Catch block is applicable when you use try block in the T-SQL. It catches errors raised by a statement.

Syntax

BEGIN CATCH

<Statements>

END CATCH

RAISERROR

We can also send our custom error message to catch block in the T-SQL.

Example

This well-known example will help you to understand how Try…Catch block perform its task.

begin try
select 1/0
end try

begin catch
select ‘Alert, error occurred’
end catch

When you execute this code you will see the message given in the catch block. But, what Try block cannot do for now. If you try to catch error using SQL Server Agent job stored procedures then you cannot catch the error message with the catch block.

For instance,

I have created a job with a job step T-SQL statement waitfor delay ’00:00:14′. Even if we try to execute the job using sp_Start_Job procedure twice with Try…Catch block, we will not be able to catch the error in the catch block.

Sample code: Create a job with a T-SQL statement given above and execute this code twice.

begin try
exec msdb.dbo.sp_start_job ‘Test_Job’
end try

begin catch
print ‘Alert, error occurred’
end catch

Output:

Run 1

Run 2

Thanks for reading.