Handling error in SQL SERVER 2012 Part -1

Introduction

Error handling is an essential in any programming language. And naturally you have to implement error handling in your T-SQL to build solid SQL SERVER code. Before the release of SQL SERVER 2005 from Microsoft, you are able to handle errors in your T-SQL code using @@Error. But later you are handling error how you are doing in your procedure languages like c#, Java using TRY...CATCH block.

I will do sample code using Adventureworks2012 database. It's free database from microsoft. You can download Adventureworks2012 database from here.

Make sure you selected correct version of your version of SQL SERVER (If you are not using SQL SERVER 2012). 

TRY...CATCH Block

You can try executing some statements in TRY block and handling errors in the CATCH block if they occur.

Therefore, simplest error handling structure can be like this :

• TRY - Try executing statements.
• CATCH - Handle the errors if occurs.

First and second lines of code are for which database you are using. 

using Advetureworks2012

Go

Next line is SET NOCOUNT ON - Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

You can see details about SET NOCOUTN ON/OFF from below link.

http://www.techwalkin.com/2012/01/what-is-set-nocount.html

From Next line try block starts and there is select statement where number 10 is divided by 0.

The select statement will generate error. 

After end of try block, Catch block starts and here I am writing print statement, which is printing error message.

In next part I will explain further details regarding error handling.