如何跨两个存储过程将主键值传递给外键？ [英] How do I pass primary key values to foreign key across two stored procedures?

问题描述

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 .

推荐答案

使用OUTPUT参数或返回码：从存储过程返回数据Microsoft Docs [ ^ ]

第一个过程返回ID值，然后将其传递给第二个作为正常参数。

（这样，您可以使用相同的第二个SP以后在表格中添加其他条目）

Either use a OUTPUT parameter, or a return code: Return Data from a Stored Procedure | Microsoft Docs[^]
The first procedure returns the ID value, and you then pass that to the second as a normal parameter.
(And that way, you can use the same second SP to add other entries to the table later)

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