Transact-SQL : Try-Catch Block

SQL-Tables
SQL-Tables

Transact-SQL : Try-Catch Block

Welcome to CodeSpread!

Similar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors.

A TRY-CATCH block consists of two parts: a TRY block and a CATCH block.

  • A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement. In between the two statements, A group of SQL statements or stored procedure, or trigger are placed. For example,

BEGIN TRY

SELECT * FROM Employee WHERE EmployeeID=101

END TRY

Don’t Worry, we are not expecting an error in the above statement too!

  • A TRY block must be immediately followed by a CATCH block.
  • A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement.CATCH block uses the few error functions to capture error information, For Example,

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber;

END CATCH

A combined Example might look like,


BEGIN TRY

SELECT * FROM Employee WHERE EmployeeID=101

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber;

END CATCH

Few Points about Try-Catch

  • A TRY block must be immediately followed by a CATCH block.
  • They cannot be nested.
  • Error Severity of 10 or lower is not handled.
  • Error Severity of 20 or higher which causes the database to close the connection, is not handled.
  • In case of No error, Catch is not executed.

Few Error Functions available in Catch block


BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

  • ERROR_NUMBER() returns the error number of the error message.
  • ERROR_MESSAGE() returns message text of the error.
  • ERROR_SEVERITY() returns the error severity.
  • ERROR_STATE() returns the error state number.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

Another option:

@@ERROR generates error information immediately after the Transact-SQL statement that generated the error. If the error used an error message defined in sys.messages, we can retrieve the defined severity and error message text from sys.messages.


DECLARE @ErrVar INT;

SET @ErrVar = @@ERROR;

SELECT @ErrVar AS ErrID, text

FROM sys.messages WHERE message_id = @ErrVar ;

Try-Catch Block is a powerful option to detect and handle errors in a simple and flexible way.

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • Questions related to Connection Pooling in .NET Questions related to Connection Pooling in .NET What is Connection Pooling in .NET? Here, the term connection refers to connecting to a data source and Pooling means grouping together of resources. So, Connection + Pooling means grouping toget...
  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • VB.Net: Download file from FTP and UnZipVB.Net: Download file from FTP and UnZipExperience: Many application requires data migration tasks to execute on daily basis. To achieve this we generally seek help of SSIS package, if at all we are making use of SQL sever. ...
  • Few lines about SQL Use statementFew lines about SQL Use statement We see this statement in Management Studio now and then and it is used quite extensively. For Example, [code] USE MyDatabaseName GO [/code] MyDatabaseName : MyDatabaseName is the n...