如何在存储过程中通过sp_executesql获取最后插入的identity值

问题的核心原因很明确：sp_executesql会创建一个独立的执行作用域，你在外层存储过程里调用SCOPE_IDENTITY()，只能获取当前存储过程作用域内生成的标识值，而动态SQL里的INSERT操作是在另一个作用域完成的，所以外层自然拿不到结果。

下面是两种可靠的解决方式，推荐第一种：

方法1：通过OUTPUT参数传递标识值 #

我们可以在动态SQL内部获取插入的标识值，然后通过sp_executesql的输出参数把值传递回外层存储过程。修改后的存储过程代码如下：

ALTER proc [dbo].[spHoldTransaction] 
    @RegisterNo int, 
    @StoreID int, 
    @Department varchar(50), 
    @TransactionDateFrom date, 
    @TransactionDateTo date, 
    @Comment Varchar(50) 
AS 
BEGIN 
    DECLARE @RegisterID int;
    DECLARE @DatabaseName varchar(15);
    DECLARE @Batch int;
    DECLARE @LastInsertedID int; -- 用于接收返回的标识值

    SELECT @RegisterID=ID FROM Register WHERE Register.Number = @RegisterNo;
    SELECT @Batch = BatchNumber From Batch WHERE Status = 0 and RegisterID = @RegisterID
    SET @DatabaseName = 'xxx'
    -- 注意：你这里处理了@Department变量但没在INSERT中使用，是不是代码遗漏了？如果没用可以考虑删除
    SELECT @Department=''''+REPLACE(@Department,',',''',''')+''''

    DECLARE @Qry nvarchar(MAX);
    DECLARE @ParamDefinition nvarchar(MAX);

    -- 新增输出参数的定义
    SET @ParamDefinition = N'@comment nvarchar(50),@StoreID int,@Batch int, @LastInsertedID int OUTPUT'
    
    -- 修改动态SQL，在INSERT后获取当前作用域的标识值并赋值给输出变量
    SET @Qry = ' 
        INSERT INTO '+@DatabaseName+'.dbo.TransactionHold ( 
            [StoreID] ,[HoldComment] ,[BatchNumber] ,[ShippingNotes] 
        ) 
        SELECT 
            @StoreID AS [StoreID] 
            ,@Comment AS [HoldComment] 
            ,@Batch AS [BatchNumber] 
            ,'''' AS [ShippingNotes]; 
        -- 在动态SQL的作用域内获取标识值
        SET @LastInsertedID = SCOPE_IDENTITY(); 
    ' 

    -- 执行动态SQL，指定输出参数
    EXECUTE sp_executesql @Qry, @ParamDefinition, 
        @Comment = @Comment, 
        @StoreID = @StoreID, 
        @Batch = @Batch, 
        @LastInsertedID = @LastInsertedID OUTPUT

    -- 返回最终获取到的标识值
    SELECT @LastInsertedID AS LastInsertedIdentity
END 

为什么这样有效？ #

• SCOPE_IDENTITY()在动态SQL内部执行时，获取的是该作用域内INSERT操作生成的标识值
• 通过OUTPUT参数，我们把这个值传递回了外层存储过程的变量中，最后就可以正常返回了

方法2：在动态SQL中直接返回标识值 #

如果你不需要在存储过程中使用这个值，只是要返回给调用方，也可以直接在动态SQL里执行SELECT SCOPE_IDENTITY()：

-- 只修改动态SQL和执行部分
SET @Qry = ' 
    INSERT INTO '+@DatabaseName+'.dbo.TransactionHold ( 
        [StoreID] ,[HoldComment] ,[BatchNumber] ,[ShippingNotes] 
    ) 
    SELECT 
        @StoreID AS [StoreID] 
        ,@Comment AS [HoldComment] 
        ,@Batch AS [BatchNumber] 
        ,'''' AS [ShippingNotes]; 
    -- 直接在动态SQL里返回标识值
    SELECT SCOPE_IDENTITY() AS LastInsertedIdentity
' 

-- 执行时直接获取返回结果
EXECUTE sp_executesql @Qry, @ParamDefinition, @Comment, @StoreID, @Batch

这种方式更简洁，但如果后续需要在存储过程中对这个标识值做进一步处理，还是方法1更灵活。

另外提醒一下：你代码里的@Department变量做了字符串替换处理，但没有在INSERT语句中使用，这可能是一个遗漏，建议检查一下逻辑是否完整。

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