Home > SQL Server > SQL Server Exception handling

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.

 

Advertisements
  1. Romex
    November 27, 2011 at 10:57 pm

    How to notify the user when a ETL compilation get fail.

    • November 30, 2011 at 9:13 pm

      Sorry for the delay reply.

      you can use ontaskfailed event in the SSIS and send an email to the user(s) via Send Email task.

  2. December 3, 2011 at 12:07 am

    Nice article..i love sql server discussion

  3. Rajesh Singh
    December 26, 2011 at 8:55 am

    Very informative post. Its really helpful for me and beginner too. Check out this link too its also having a nice post related to this post over the internet which also explained very well…

    http://mindstick.com/Articles/8da50627-0abd-448d-a100-abe206bf7f66/?Exception%20handling%20in%20SQL%20Server

    Thanks

  1. September 12, 2013 at 11:26 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: