如何跨两个存储过程将主键值传递给外键？

问题描述

I want  to Pass identity primary key values to foreign key using two separate stored procedures.One stored procedure will contain the insert into table with primary key while the other will contain the insert into the table with the foreign key.

Below is what I have done but it is not working.

USE [Apartmentmanagementsystem]
GO
/****** Object: StoredProcedure [dbo].[SPnew1] Script Date: 6/24/2018 11:53:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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),
@lease_id int output,
@initial_date varchar(255),
@end_date varchar(255),
@deposit varchar(255)

as

begin 
set nocount on
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 dbo.Lease (initial_date,end_date,deposit,tenant_id)values(@initial_date,@end_date,@deposit,@tenant_id);
select @lease_id=SCOPE_IDENTITY()

end

USE [Apartmentmanagementsystem]
GO
/****** Object: StoredProcedure [dbo].[Rento] Script Date: 6/24/2018 11:54:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Rento]
@rent_id int,
@rent_fee varchar (255),
@late_fee varchar (255),
@due_date varchar (255),
@service_charge varchar (255),
@pay_id int,
@pay_date varchar (255),
@pay_amount varchar (255),
@receipt_no varchar (255)


as
begin
set nocount on
declare @lease_id int 
select @lease_id=SCOPE_IDENTITY();
insert into Rent ( rent_fee,late_fee,due_date,service_charge,lease_id) values (@rent_fee,@late_fee,@due_date,@service_charge,@lease_id)
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

within the stored procedures the  @tenant_id=scope identity ()  and @rent_id=scope identity () are working but @lease_id=SCOPE_IDENTITY() is not working across the two stored procedures.

我尝试过：



我尝试使用@@ IDENTITY代替@lease_id只获取空值。

What I have tried:

I have tried using @@IDENTITY in place of @lease_id only to get null values .

推荐答案

这篇关于如何跨两个存储过程将主键值传递给外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！