This blog is part of the series The TSQL Classes: Stored Procedure

The link to previous post are given below.

In this class I will talk about ways to handle error in a stored procedure.

Error/Exception handling is an important part of programming whether it’s T-SQL /.Net. There are two main methods to handle errors in stored procedures.

  1. @@ERROR
  2. TRY and CATCH Block

@@Error Global Variable

The @@ERROR (Global variable) returns the error number for the last Transact-SQL statement executed. This can be used in IF / ELSE block to check if error occurred and take appropriate action accordingly. The value of @@error gets reset after every statement. Thus it’s advisable to have it stored in a variable.

Let’s see how @@ERROR is used to check errors.

The above procedure inserts a value in Sales.Currency table.  It then checks the value in @@Error global variable. A nonzero value means an error and a value of 0 indicates success. Let’s now execute the procedure and observe value returned by @@Error.


The Sales.Currency table has a primary key constraint on CurrencyCode column which means that it can’t contain duplicate values. The procedure is thus executed twice to force a primary key violation error. The first execution of the procedure inserts a record successfully in Sales.Currency table and thus @@Error returns a value of 0, where as the second execution fails with a primary key violation error and returns error number 2627, which is equivalent to the value returned in error under message tab (highlighted in yellow).The procedure can further modified to return custom message or specific return value by checking the value in @@Error variable.

In above procedure, the value returned by @@Error is assigned to a variable because its value is reset after every statement.

Try and Catch

BOL defines Try and Catch as

“It Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.” 

 Let’s modify the procedure uspAddCurrency discussed above to use Try and Catch to catch error instead of @@Error global variable.

Let’s execute the procedure and analyze the result.


The procedure errors out on second execution with a primary key violation error and control goes to catch block. The catch block returns each and every detail of the error occurred. In this case, there is no sql server error shown under message tab which was shown in case of @@Error variable.

Another important use of Try and Catch is error logging. The error returned can be logged to a table for troubleshooting purpose. This can be done by modifying the above catch block as shown below.

The error is now recorded to Errorlog table as shown in snapshot below.


Difference between @@Error and “Try and Catch”

@@Error Try and Catch
It only reports error It catches error
It’s value is set after every statement An error (not all errors) in sql statement transfer controls to catch block.
It’s difficult to maintain an error log table An error log table can be easily maintained for future analysis.

Try and Catch is the preferred way to catch and record errors and should be used as a best practise.


In this class I talked about 2 different ways to handle errors;@@Error and Try and Catch block.



Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook