How to Work with Exceptions Handling in SQL Server Stored Procedure

This article explains about the exceptions handling in sql server stored procedure.

It is required to handle exceptions in sql server when we are performing multiple DML(Data Manipulation language) operations in a single stored procedure.

For example: A stored procedure containing three insert statements in on stored procedure, such that each has dependency on one another like the identity value generated for the id (primary key ) in first insert statement, need to get inserted in the second insert statement in the same stored procedure.

Here, in this case if first statement is executed successfully and an error has occurred in the second statement then there we get the problem.

So, here by exception handling we can rollback the transaction completely, hence no record gets inserted.

Following is the sample procedure :

Create PROCEDURE [ProcedureName]

      (

      -- parameters

      @msg int output

      )

AS

Begin
  begin try 

      begin transaction Trans1          

           -- Your DML operations goes here                    

       set @msg=1

      commit transaction Trans1                      

  end try

  begin catch    

      rollback transaction Trans1

      set @msg=2

  end catch 

END