Retrieving auto-incremented column value from table where multiple insert/selects in single stored procedure

Question

I have a stored procedure with multiple insert/select statements. Let's say I'm using the first insert to populate a "Manager" table. On insert, a ManagerId (incremented automatically) is added, but not referenced in the insert statement. I then wish to use the ManagerId from this table to insert a row into another table, where ManagerId is a foreign key. Sample code as follows..

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptInsertNewManager]
    -- Add the parameters for the stored procedure here
    @FName varchar(50),
    @LName varchar(50),
    @EMail varchar(100),   
    @UserRoleID int,
    @LANUserID varchar(25), 
    @GroupID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
INSERT INTO [Manager]           
          ([FName], 
           [LName],
           [Email], 
           [UserRoleID],
           [LANUserID],          
           [ActiveFlag],
           [GroupID]
)
     VALUES
            (@FName
            ,@LName
            ,@EMail
            ,@UserRoleID
            ,@LANUserID
            ,1 
            ,@GroupID);

COMMIT

SELECT ManagerId FROM [Manager] AS newManager WHERE LANUserID = @LANUserID; 
        --also insert into Users table.
    INSERT INTO [dbo].[aspnet_Users] (
        [UserId],
        [UserName],
        [LoweredUserName],
        [ManagerId]
    )
        VALUES (
            NEWID(),
            @LANUserID,
            LOWER(@LANUserID),
            newManager)
END

This, obviously, does not work. This was my attempt at solving this. I'm fairly new to SQL, so any help with this problem would be greatly appreciated.

Answer 1

use scope_identity() after your insert to capture the most recent single identity value from within your current scope:

DECLARE @ID     int


INSERT ......
SELECT @ID=scope_identity()

use @ID wherever you need it

note: SCOPE_IDENTITY() is preferred over the older @@IDENTITY because it gives the last Identity value in the current scope, which avoids issues from triggers that insert into log tables (with identities).

However, if you need multiple identity values (inserting a set of rows), use OUTPUT and INTO:

declare @test table (RowID  int identity(1,1) primary key not null, RowValue varchar(10) null)
declare @OutputTable table (RowID int not null)

insert into @test (RowValue)
    OUTPUT INSERTED.RowID
    INTO @OutputTable
    SELECT 'A'
    UNION SELECT 'B'
    UNION SELECT 'C'
    UNION SELECT 'D'
    UNION SELECT 'E'


select * from @OutputTable

the output:

(5 row(s) affected)
RowID
-----------
1
2
3
4
5

(5 row(s) affected)

Answer 2

for MS Sql Server

Whenever you insert a record and it has auto-increment column (Identity Column in ms-sql parlance) you can user this to retrieve the id for the row you inserted:

@id = SCOPE_IDENTITY()

this ensures that you get the identity column value that your insert produced, not by the other users during the time your code was running (between insert and getting identity by scope_identity())

Answer 3

Yuck... you have your proc and transaction all twisted up...

You want these two inserts to occur within the same transaction (which I am deducing from the COMMIT statement in the middle of your original proc). So you need to appropriately place a BEGIN TRANSACTION and COMMIT statement around all of your INSERT and SELECT statements to isolate the data change.

Consider the following:

CREATE PROCEDURE [dbo].[sptInsertNewManager]
    -- Add the parameters for the stored procedure here
    @FName varchar(50),
    @LName varchar(50),
    @EMail varchar(100),   
    @UserRoleID int,
    @LANUserID varchar(25), 
    @GroupID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @ManagerID  INT

BEGIN TRANSACTION    

    -- Insert statements for procedure here
INSERT INTO [Manager]           
          ([FName], 
           [LName],
           [Email],     
           [UserRoleID],
           [LANUserID],          
           [ActiveFlag],
           [GroupID]
)
     VALUES
                (@FName
                ,@LName
                ,@EMail
            ,@UserRoleID
            ,@LANUserID
                ,1 
            ,@GroupID);

-- Collect the ID you just created
SELECT @ManagerID = SCOPE_IDENTITY()


        --also insert into Users table.
    INSERT INTO [dbo].[aspnet_Users] (
        [UserId],
        [UserName],
        [LoweredUserName],
        [ManagerId]
    )
        VALUES (
                NEWID(),
                @LANUserID,
                LOWER(@LANUserID),
                @ManagerID)   -- This is the new identity you just created

COMMIT

END