CODEWITHSUNDEEP
sqlsql-server
Gavin O'Doherty
Gavin O'Doherty

Reputation: 61

How do I retrieve automatically generated ID values when adding rows to multiple tables using a stored procedure?

I'm currently working on a database design for a Gym in SQL Server (ERD at following link: https://1drv.ms/u/s!ApYj_1gaRPZGgnjOj6VC_HgeaenU).

As part of this I need to be able to create a new member and their first program via a stored procedure. The issue I'm facing is that in order to create a new program, I first need the member number which is automatically generated when a row is added to the Member table, and in order to create a new member, I need to have a payment ID which is automatically generated when a row is added to the payment table.

How do I retrieve the automatically generated identity values in a single stored procedure? i.e. the only way I can see to do this at the moment is to first create a stored procedure to create a row in the Payment table, then manually look the PaymentID value up and then use this value in a separate stored procedure for inserting a row in the Member table (and same again for setting up a new Program).

See below the 3 separate stored procedures I have currently for this, any help would be greatly appreciated.

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.sstatic.net/ERuFS.png

Upvotes: 0

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You need to return the value from the stored procedure. As a general rule, I recommend using the output clause for this:

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;

The use of @@ERROR seems awkward to me (which is one reason I removed it). If you really want to handle errors, then you want to be using TRY/CATCH blocks.

.

Upvotes: 1

Related Questions