Passing primary key values in the parent table to foreign key values in child table with parent insert and child insert being in separate stored procedures.
Parent is Lease table, the child is Rent table. I am getting null values for lease_id in Rent. Why?
Parent's stored procedure
ALTER procedure [dbo].[SPnew1]
@tenant_id int,
@F_Name varchar(255),
@L_Name varchar(255),
@Phone varchar(255),
@Email varchar(255),
@Age int,
@Gender varchar(255),
@initial_date varchar(255),
@end_date varchar(255),
@deposit varchar(255)
as
if @tenant_id=0
begin
insert into dbo.Tenant
(F_Name,L_Name,Phone,Email,Age,Gender)
values( @F_Name,@L_Name,@Phone,@Email,@Age,@Gender )
select @tenant_id=SCOPE_IDENTITY()
insert into Lease (initial_date,end_date,deposit,tenant_id)values(@initial_date,@end_date,@deposit,@tenant_id);
end
child's stored procedure
ALTER procedure [dbo].[Rento]
@rent_id int,
@rent_fee varchar (255),
@late_fee varchar (255),
@due_date varchar (255),
@service_charge varchar (255),
@lease_id int,
@pay_id int,
@pay_date varchar (255),
@pay_amount varchar (255),
@receipt_no varchar (255)
as
begin
insert into Rent ( rent_fee,late_fee,due_date,service_charge,lease_id) values (@rent_fee,@late_fee,@due_date,@service_charge,@@identity)
select @rent_id=SCOPE_IDENTITY();
insert into Payment (pay_date,pay_amount,receipt_no,rent_id) values(@pay_date,@pay_amount,@receipt_no,@rent_id)
end
1 Answer(s)