Add TRY CATCH block to SQL Stored Procedure

In this article I describe exceptional handling using TRY..CATCH block in SQL Server 2008
  • 3092

Introduction

SQL Server provides a way for exceptional handling i.e. TRY...CATCH method. It is used to handle SQL Server errors. TRY..CATCH block allows you to specify two SQL statements. The code to be executed is enclosed in TRY block and the code for handling error is enclosed under CATCH block. If the code under the TRY block is failed then control automatically jumps under the CATCH block. CATCH block is used to catch errors. Some conditions for try catch block are given below:

  • TRY...CATCH block could contain single SQL statements or it contains multiple SQL statements.
  • Catch block should come immediately after TRY block
  • We could not span if or else statement under TRY and CATCH block.

Syntax:

create procedure Procedure_Name
as
BEGIN TRY
{ sql statement}
END TRY
BEGIN CATCH
{ sql statement}
END CATCH

Example:

Crete a procedure named Procedure_Try_Catch and in TRY block we specify code which will generate error i.e. divide by 0. Now write following code to create procedure.

CREATE PROCEDURE Procedure_Try_Catch
AS
BEGIN TRY
SELECT 1/0 -- dividing by zero to generate error
END TRY
BEGIN CATCH
PRINT 'control comes under the catch block'
END CATCH

Execute above stored procedure.

Output:

TryBlock-in-sql.jpg

While executing Procedure_Try_Catch procedure, an error occurs in TRY block, so control is jumped under the CATCH block and code under CATCH block is executed. to see information about error, print various error functions defined in below example:

create procedure try_catch
as
begin try
select 2/0 as value
end try
begin catch
select ERROR_NUMBER() as ErrorNumber,
ERROR_STATE() as ErrorState,
ERROR_MESSAGE() as ErrorMessage,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine
end catch

Execute above procedure.

ErrorFunctions-using-TryCatch.jpg


© 2018 DotNetHeaven. All rights reserved.