SQL

Question

Alter PROCEDURE Add_Product_Service_Details
(
@Flag varchar(30)='',
@servicemasterid int = 0,
@sub_id int = 0,
@packname varchar(50)='',
@packtype varchar(50)='',
@pid int = 0,
@Opid int = 0,
@serviceid varchar(100)='',
@eup int = 0,
@validity int = 0
)
AS
BEGIN
If @Flag = 'ADD'
Begin
declare @inserterror int
SET @inserterror = 0
Begin Transaction
Insert into Int_Prod_ServiceMaster (sub_id,pack_name,pack_type,pid) values (@sub_id,@packname,@packtype,@pid)
declare @svcmasterid int
set @svcmasterid = SCOPE_IDENTITY()
Insert into Int_Prod_ServiceDetails (servicemaster_id,service_id,parent_service_id,eup,validity,pid) values (@svcmasterid,@serviceid,@serviceid,@eup,@validity,@pid)
SET @inserterror = @@ERROR
If @inserterror <> 0
Begin
ROLLBACK
END
Else
Begin
Commit
END
END
Else If @Flag='Get_OperatorId'
Begin
Select Distinct op_Id From Int_ProductMaster
END
Else If @Flag='Get_Product'
Begin
Select prod_name,pid from Int_ProductMaster where op_id = @Opid
END
Else If @Flag = 'Get_Sub_Details'
Begin
Select sub_Id,sub_name from [local_shemaroomob].[dbo].[mobile_sub_details] where op_id=@Opid
END
END



I have two tables
Int_Prod_ServiceMaster
columns are
servicemasterid int primary key,sub_id int,pack_name varchar,pack_type varchar,pid int

second table is Int_Prod_ServiceDetails
columns are
id int primary key,servicemasterid int foriegn key,serviceid varchar,parentserviceid varchar,eup int,validity int,pid int
here (serviceid + parentserviceid is a unique key)
Now while insertion i am inserting

servicemasterid,subid,packname,packtype,pid in int_prod_servicemaster table and then i am taking last inserted servicemasterid from int_prod_Servicemaster
table and inserting servicemasterid,serviceid,parentserviceid,eup,validity,pid into Int_Prod_ServiceDetails

i have a case if where more than 1 record in the Int_Prod_ServiceDetails table which have same serviceid and parentserviceid then the record is not inserted
in int_prod_servicedetails as (Serviceid and parentserviceid) is unique key,that is second insert statement fails,but first insert statement executes,i want
the entire transaction to rollback if second insertion statement fails.how should i do this,please do help me for the same.


i have tried the above code.