locked
Pass a new identity seed number from top to the bottom insert in a stored procedure. RRS feed

  • Question

  • User1793652459 posted

    Hello,

    I have a requirement to store a request data and it's related attachments.  I wrote a stored procedure to insert the request data in one table and then it's attachments in a separate table.  However I can't figure out, how to pass the newly created identity seed number to the attachments table? So I can link attachments with the request table via a primary/foreign key relationship.  Here is a simplified version of the stored procedure, I have removed some of the fields to keep the code smaller:

    CREATE PROCEDURE dbo.spCreateRequest

    @IDNo varchar(50),
    @Creator varchar(100),
    @Status varchar(50),
    @Location varchar(50),
    @Comments varchar(200),
    @File varchar(250),
    @FileData varbinary(max)

    AS

    SET NOCOUNT ON

    BEGIN

    INSERT INTO dbo.Requests
    (
    IDNo ,
    Creator ,
    Status ,
    Location,
    Comments

    )
    VALUES
    (
    @IDNo,
    @Creator,
    @Status ,
    @Location ,
    @Comments
    )

    -- Next need to pass a number generated by an identity seed column (ReqID) in the top table to this second insert statement

    INSERT INTO dbo.Attachments
    (
    ReqID ,
    IDNo ,
    [File] ,
    FileData
    )
    VALUES
    (
    --(identity seed column value generated from above insert statement),
    @IDNo,
    @File ,
    @FileData
    )

    END

    Thank you.

    Monday, November 13, 2017 9:34 PM

Answers

  • User991499041 posted

    Hi johnzee,

    However I can't figure out, how to pass the newly created identity seed number to the attachments table? So I can link attachments with the request table via a primary/foreign key relationship. 

    You can use SCOPE_IDENTITY() to get the last auto generated ID withing the scope which is this stored proc in this case.

    It will be something like below.

    INSERT INTO dbo.Requests
    (
    IDNo ,
    Creator ,
    Status ,
    Location,
    Comments 
    
    )
    VALUES
    (
    @IDNo,
    @Creator,
    @Status ,
    @Location ,
    @Comments
    )
    
    DECLARE @ReqID int
    SET @ReqID = SCOPE_IDENTITY() 
    
    -- Next need to pass a number generated by an identity seed column (ReqID) in the top table to this second insert statement
    
    INSERT INTO dbo.Attachments
    (
    ReqID ,
    IDNo 
    ...
    )
    VALUES
    (
    @ReqID,--(identity seed column value generated from above insert statement),
    @IDNo
    ...
    )

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 14, 2017 1:55 AM

All replies

  • User991499041 posted

    Hi johnzee,

    However I can't figure out, how to pass the newly created identity seed number to the attachments table? So I can link attachments with the request table via a primary/foreign key relationship. 

    You can use SCOPE_IDENTITY() to get the last auto generated ID withing the scope which is this stored proc in this case.

    It will be something like below.

    INSERT INTO dbo.Requests
    (
    IDNo ,
    Creator ,
    Status ,
    Location,
    Comments 
    
    )
    VALUES
    (
    @IDNo,
    @Creator,
    @Status ,
    @Location ,
    @Comments
    )
    
    DECLARE @ReqID int
    SET @ReqID = SCOPE_IDENTITY() 
    
    -- Next need to pass a number generated by an identity seed column (ReqID) in the top table to this second insert statement
    
    INSERT INTO dbo.Attachments
    (
    ReqID ,
    IDNo 
    ...
    )
    VALUES
    (
    @ReqID,--(identity seed column value generated from above insert statement),
    @IDNo
    ...
    )

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 14, 2017 1:55 AM
  • User1793652459 posted

    Thank you. 

    Saturday, November 18, 2017 2:19 AM