sql - 从表中检索自动递增列值，其中单个存储过程中有多个插入/选择

Question

我有一个包含多个插入/选择语句的存储过程。假设我正在使用第一个插入来填充“Manager”表。插入时，会添加 ManagerId(自动递增)，但不会在插入语句中引用。然后，我希望使用该表中的 ManagerId 将一行插入到另一个表中，其中 ManagerId 是外键。示例代码如下..

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

这显然是行不通的。这是我解决这个问题的尝试。我对 SQL 相当陌生，因此我们将不胜感激有关此问题的任何帮助。

Answer 1

在插入后使用scope_identity()来捕获当前范围内的最新单个身份值:

DECLARE @ID     int


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

在任何需要的地方使用@ID

注意:SCOPE_IDENTITY() 优于旧的 @@IDENTITY，因为它给出当前作用域中的最后一个 Identity 值，这避免了插入日志表(带有身份)的触发器引起的问题。

但是，如果您需要多个标识值(插入一组行)，请使用 OUTPUT 和 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

输出:

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

(5 row(s) affected)