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

)

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