how to rollback changes in stored procedure

See more:

SQL-Server-2005

SQL-Server

Hi,
I have two table OPDPatient And Registration these two table tables are linked via REGNO.
Now I want to update records of both table. For this I first deleted all the record of that particular REGNO which lies in both tables.Now i want if record of first table is deleted successfully then only second table will go for delete and then update operation occur like this:

ALTER proc Sp_Update_OPD_Registration
(
@PID varchar(30),
@Department int,
@Doctor varchar(20),
@ReffBy varchar(50),
@Diagnosis nvarchar(150),
@Category int ,
@MLC char(3),
@PoliceStation varchar(100),
@FIRno varchar(100),
@Identification varchar(100),
@Injury varchar(100),
@Signature varchar(100),
@MLC_Charge varchar(150),
@Willing varchar(50),
@Remark varchar(500),
@broughtby varchar(50),
@contactno varchar(50),
@DoctorFee money,
@AddFee money,
@Reason varchar(100),
@Discount money
)
as
Declare @result tinyint

if(@PID <>'' or @PID<> null)
BEGIN
Delete from OpdPatient where RegNo=@PID--if it is successfull
Delete from Registration where RegNo=@PID--then this delete occur
-- then this update operation
update OpdPatient set
Department=@Department,Doctor=@Doctor,ReffBy=@ReffBy,Diagnosis=@Diagnosis,Category=@Category,MLC=@MLC,PoliceStation=@PoliceStation,FIRno=@FIRno,Identification=@Identification,Injury=@Injury,Signature=@Signature,MLC_Charge=@MLC_Charge,Willing=@Willing,Remark=@Remark,broughtby=@broughtby,contactno=@contactno,DoctorFee=@DoctorFee,AddFee=@AddFee,Reason=@Reason,Discount=@Discount where RegNo=@PID
END