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