使用存储过程向多个表添加行时，如何检索自动生成的 ID 值?

Question

我目前正在为SQL Server中的健身房进行数据库设计(ERD在以下链接：https://1drv.ms/u/s!ApYj_1gaRPZGgnjOj6VC_HgeaenU(。

作为其中的一部分，我需要能够通过存储过程创建一个新成员及其第一个程序。我面临的问题是，为了创建一个新程序，我首先需要将行添加到 Member 表时自动生成的成员编号，为了创建新成员，我需要有一个付款 ID，该 ID 是在将行添加到付款表时自动生成的。

如何在单个存储过程中检索自动生成的标识值？ 即目前我可以看到的唯一方法是首先创建一个存储过程以在付款表中创建一行，然后手动查找 PaymentID 值，然后在单独的存储过程中使用此值在 Member 表中插入一行(同样用于设置新的程序(。

请参阅下面我目前为此拥有的 3 个单独的存储过程，任何帮助将不胜感激。

CREATE PROCEDURE NewPayment
@PaymentType varchar(10),
@LastPaymentDate date,
@AmountPaid varchar(15),
@NextPaymentDate date,
@AmountDue varchar(15)
AS
INSERT INTO Payment 
VALUES (@PaymentType, @LastPaymentDate, @AmountPaid, @NextPaymentDate, @AmountDue);
IF @@ERROR <> 0 
RETURN -1 
ELSE 
RETURN 0
GO
CREATE PROCEDURE NewMember
@Name varchar(100),
@Address varchar(200),
@ContactNo varchar(15),
@Email varchar(50),
@Photo image,
@PaymentID int,
@GDPRQ1 char(1),
@GDPRQ2 char(1),
@IsDeleted char(1)
AS
INSERT INTO Members 
VALUES (@Name, @Address, @ContactNo, @Email, @Photo, @PaymentID, @GDPRQ1, @GDPRQ2, @IsDeleted);
IF @@ERROR <> 0 
RETURN -1 
ELSE 
RETURN 0
GO
CREATE PROCEDURE NewProgram
@MemberNumber int,
@TrainerID int,
@ProgramStartDate date,
@TrainerReviewDate date,
@Active char(1)
AS
INSERT INTO Program 
VALUES (@MemberNumber, @TrainerID, @ProgramStartDate, @TrainerReviewDate, @Active);

[1]: https://i.stack.imgur.com/ERuFS.png

Answer 1

您需要从存储过程返回值。 作为一般规则，我建议为此使用output子句：

create proc NewMember (
@Name varchar(100),
@Address varchar(200),
@ContactNo varchar(15),
@Email varchar(50),
@Photo image,
@PaymentID int,
@GDPRQ1 char(1),
@GDPRQ2 char(1),
@IsDeleted char(1),
@MemberId_out int output
) As
begin
declare table @id (memberid int)
Insert into Members
output inserted.memberid into @id
values (@Name, @Address, @ContactNo ,@Email, @Photo, @PaymentID, @GDPRQ1,@GDPRQ2, @IsDeleted);
select @MemberId_out = memberid from @id;
return 0
end;

使用@@ERROR对我来说似乎很尴尬(这是我删除它的原因之一(。 如果你真的想处理错误，那么你希望使用TRY/CATCH块。

.