如何在 stored procedure 中获取多表自动生成的 ID

2026-10-24支付小周

结论

可以把三次 INSERT 写在同一个存储过程中。每次插入后，立即用 SCOPE_IDENTITY() 获取当前作用域生成的 identity value：

插入 Payment，获取 PaymentID。
使用 PaymentID 插入 Members，获取 MemberNumber。
使用 MemberNumber 插入 Program。

这三次操作应放在同一个事务中，并用 TRY...CATCH 处理错误。只要其中一步失败，事务就会回滚，避免留下只有付款记录，却没有对应会员或训练计划的不完整数据。

存储过程示例

以下示例假设：

Payment.PaymentID 是 identity 列。
Members.MemberNumber 是 identity 列。
Program 表不需要返回新生成的 ID。
示例中的列名与实际表结构一致。

CREATE PROCEDURE dbo.CreateMemberWithProgram
    -- Payment
    @PaymentType       varchar(10),
    @LastPaymentDate   date,
    @AmountPaid        decimal(12, 2),
    @NextPaymentDate   date,
    @AmountDue         decimal(12, 2),

    -- Member
    @Name              varchar(100),
    @Address           varchar(200),
    @ContactNo         varchar(15),
    @Email             varchar(50),
    @Photo             varbinary(max),
    @GDPRQ1            char(1),
    @GDPRQ2            char(1),
    @IsDeleted         char(1),

    -- Program
    @TrainerID         int,
    @ProgramStartDate  date,
    @TrainerReviewDate date,
    @Active            char(1)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @PaymentID int;
    DECLARE @MemberNumber int;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.Payment
        (
            PaymentType,
            LastPaymentDate,
            AmountPaid,
            NextPaymentDate,
            AmountDue
        )
        VALUES
        (
            @PaymentType,
            @LastPaymentDate,
            @AmountPaid,
            @NextPaymentDate,
            @AmountDue
        );

        SET @PaymentID = CONVERT(int, SCOPE_IDENTITY());

        INSERT INTO dbo.Members
        (
            Name,
            Address,
            ContactNo,
            Email,
            Photo,
            PaymentID,
            GDPRQ1,
            GDPRQ2,
            IsDeleted
        )
        VALUES
        (
            @Name,
            @Address,
            @ContactNo,
            @Email,
            @Photo,
            @PaymentID,
            @GDPRQ1,
            @GDPRQ2,
            @IsDeleted
        );

        SET @MemberNumber = CONVERT(int, SCOPE_IDENTITY());

        INSERT INTO dbo.Program
        (
            MemberNumber,
            TrainerID,
            ProgramStartDate,
            TrainerReviewDate,
            Active
        )
        VALUES
        (
            @MemberNumber,
            @TrainerID,
            @ProgramStartDate,
            @TrainerReviewDate,
            @Active
        );

        COMMIT TRANSACTION;

        -- 将生成的标识值返回给调用方
        SELECT
            @PaymentID AS PaymentID,
            @MemberNumber AS MemberNumber;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;
GO

调用存储过程时，无需手动查询中间生成的 ID：

EXEC dbo.CreateMemberWithProgram
    @PaymentType       = 'Monthly',
    @LastPaymentDate   = '2026-09-01',
    @AmountPaid        = 50.00,
    @NextPaymentDate   = '2026-10-01',
    @AmountDue         = 0.00,
    @Name              = 'Zhang San',
    @Address           = 'Shanghai',
    @ContactNo         = '13800000000',
    @Email             = 'zhangsan@example.com',
    @Photo             = NULL,
    @GDPRQ1            = 'Y',
    @GDPRQ2            = 'Y',
    @IsDeleted         = 'N',
    @TrainerID         = 10,
    @ProgramStartDate  = '2026-09-01',
    @TrainerReviewDate = '2026-10-01',
    @Active            = 'Y';

为什么使用 `SCOPE_IDENTITY()`

SCOPE_IDENTITY() 返回当前会话、当前作用域内最后生成的 identity value，所以应当紧跟在对应的 INSERT 后调用。

不要用下面的方式获取 ID：

SELECT MAX(PaymentID) FROM dbo.Payment;

存在并发请求时，MAX() 可能会取到其他会话刚插入的 ID。

下面这种写法也不合适：

SELECT @@IDENTITY;

如果表上有触发器，而触发器又向其他 identity 表写入数据，@@IDENTITY 可能返回触发器生成的 ID。SCOPE_IDENTITY() 只读取当前作用域生成的值，更适合这个场景。

SCOPE_IDENTITY() 的返回类型是 numeric(38, 0)，因此示例将它显式转换为 int。如果实际的 identity 列是 bigint，变量和转换类型也要相应改为 bigint。

也可以使用 `OUTPUT INSERTED`

OUTPUT 能直接获取本次 INSERT 生成的值，语义也更明确。例如：

DECLARE @NewPayment TABLE
(
    PaymentID int
);

INSERT INTO dbo.Payment
(
    PaymentType,
    LastPaymentDate,
    AmountPaid,
    NextPaymentDate,
    AmountDue
)
OUTPUT INSERTED.PaymentID INTO @NewPayment (PaymentID)
VALUES
(
    @PaymentType,
    @LastPaymentDate,
    @AmountPaid,
    @NextPaymentDate,
    @AmountDue
);

SELECT @PaymentID = PaymentID
FROM @NewPayment;

每次只插入一行时，使用 SCOPE_IDENTITY() 更简洁。如果以后需要一次插入多行，则应改用 OUTPUT INSERTED。它可以返回每一行生成的 ID，而 SCOPE_IDENTITY() 只能返回最后一个 identity value。

注意事项

明确写出列名

不要省略列名：

INSERT INTO dbo.Payment
VALUES (...);

如果表中增加了列，或者列的顺序发生变化，这种写法可能导致存储过程报错，甚至把值写入错误的列。显式列出列名还能避开 identity 列。

金额不要使用 `varchar`

如果 AmountPaid 和 AmountDue 存储的是金额，应使用与表字段一致的 decimal 类型，例如：

decimal(12, 2)

使用 varchar 可能带来隐式转换、排序错误和无效金额字符串等问题。

`image` 类型应考虑替换

如果允许调整数据库结构，图片数据建议使用 varbinary(max)。SQL Server 的 image 是旧式数据类型，不适合新的表结构。如果现有字段仍是 image，参数暂时与表字段保持兼容即可。

不要依赖 `@@ERROR` 分散处理错误

@@ERROR 只反映紧邻它之前那条语句的执行结果，中间一旦加入其他语句，错误处理就容易失效。使用 TRY...CATCH、THROW 和事务，可以集中处理三次插入中的任何异常。

事务即使回滚，已经分配的 identity value 通常也不会被 SQL Server 再次使用，因此编号出现空缺属于正常现象，不应假设 identity 编号一定连续。

如何在 stored procedure 中获取多表自动生成的 ID

结论

存储过程示例

为什么使用 SCOPE_IDENTITY()

也可以使用 OUTPUT INSERTED