SQL Tips by Namwar Rizvi

June 3, 2007

Use Try Catch for better Error Handling in TSQL

Proper error handling is the crux of good programming style and it is applicable for TSQL also. Prior to SQL Server 2005 we had to check @@Error variable value after each statement to make sure no error has occurred. This approach ends up with the spaghetti style coding of GOTO statements.
With many other enhancements SQL Server 2005 has introduced a proper way of error handling with TRY CATCH blocks similar to most modern languages like C#. You can enclose your code into Try block and errors will be handled in Catch block. It gives you cleaner and more manageable code. Following is a simple example of using TRY CATCH for error handling:

–Disables intermediate SQL Server messages
Set NoCount On
Create table #tempTable
(
SalesOrderId int,
SalesOrderNumber varchar(50)
)

Begin Try

Insert into #tempTable values(‘A’,3)
End Try

Begin Catch
Print ‘Error Number:’+CAST(ERROR_Number() as varchar)
Print ‘Error Message:’+ERROR_MESSAGE()

End Catch

For further details please refer to Microsoft documentation at
How to use TRY CATCH

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: