如何在两个SQL存储过程间传递自动生成的主键值？

你的代码核心问题是第二个存储过程调用第一个时，未传入第一个存储过程必需的参数，且参数传递逻辑有误，导致执行报错。另外，为了避免出现“销售主记录插入成功，但产品销售记录插入失败”的数据不一致情况，建议添加事务处理保证操作原子性。

修正后的第一个存储过程（sp_sell） #

原逻辑无本质问题，补充事务和错误处理增强可靠性：

ALTER PROCEDURE sp_sell
(
    @idpay INT,
    @idclient INT,
    @idworker INT,
    @total SMALLMONEY,
    @date DATE,
    @idsell INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO dbo.Sell
        (
            ID_Pay,
            ID_Client,
            ID_Worker,
            Total,
            Date
        )
        VALUES
        (
            @idpay,
            @idclient,
            @idworker,
            @total,
            @date
        );

        SELECT @idsell = SCOPE_IDENTITY();
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- 抛出错误传递给调用方
    END CATCH
END

修正后的第二个存储过程（sp_prod_sell） #

需补充第一个存储过程的必填参数，正确调用并获取生成的销售主记录ID，再插入产品销售记录：

ALTER PROCEDURE sp_prod_sell
(
    -- 新增第一个存储过程所需的参数
    @idpay INT,
    @idclient INT,
    @idworker INT,
    @total SMALLMONEY,
    @date DATE,
    @id_forn_prod INT,
    @idprodsell INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @idsell INT; -- 临时存储销售主记录ID
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 调用第一个存储过程，传入参数并获取生成的@idsell
        EXEC sp_sell 
            @idpay = @idpay,
            @idclient = @idclient,
            @idworker = @idworker,
            @total = @total,
            @date = @date,
            @idsell = @idsell OUTPUT;

        -- 使用获取到的@idsell插入产品销售记录
        INSERT INTO dbo.Product_Sell
        (
            ID_sell,
            ID_Forn_Prod
        )
        VALUES
        (
            @idsell,
            @id_forn_prod
        );

        SELECT @idprodsell = SCOPE_IDENTITY();
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END

调用示例 #

执行第二个存储过程时，传入所有必填参数并接收输出结果：

DECLARE @new_prod_sell_id INT;
EXEC sp_prod_sell
    @idpay = 1,
    @idclient = 100,
    @idworker = 5,
    @total = 299.99,
    @date = '2024-05-20',
    @id_forn_prod = 20,
    @idprodsell = @new_prod_sell_id OUTPUT;

SELECT @new_prod_sell_id AS 产品销售记录ID;

关键要点 #

1. 参数完整性：第二个存储过程必须承接第一个存储过程的所有必填参数，才能完成主记录插入并获取主键。
2. 事务一致性：通过事务保证两个插入操作要么同时成功，要么同时回滚，避免数据断层。
3. 错误透传：异常捕获后抛出错误，让调用方直接感知问题所在。

内容的提问来源于stack exchange，提问作者Sergets1