MSSQL存储过程中如何获取动态表插入记录的ID并输出？

你用EXEC()执行动态SQL时，这段SQL会运行在独立的作用域里，外部的SCOPE_IDENTITY()只能捕获当前作用域生成的自增ID，自然拿不到动态插入操作产生的ID。

这里提供两种可靠的解决方式，同时建议你用参数化查询替代字符串拼接，彻底避免SQL注入风险：

方法1：用OUTPUT子句捕获ID到表变量 #

通过OUTPUT子句把插入的ID写入表变量，再从表变量中读取值赋值给输出参数：

ALTER PROC Experience
  @Subject1 INT
 ,@Subject2 INT
 ,@TableNumber INT
 ,@IDR INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回额外的行数计数信息
    
    DECLARE @TableNames NVARCHAR(100);       
    SET @TableNames = N'TableNo_' + CONVERT(NVARCHAR(50), @TableNumber);
    
    -- 定义表变量存储插入的ID
    DECLARE @InsertedIDs TABLE (ID INT);
    
    -- 构建参数化动态SQL，用OUTPUT将ID写入表变量
    DECLARE @CMDS NVARCHAR(MAX);           
    SET @CMDS = N'INSERT INTO ' + QUOTENAME(@TableNames) + N' (Subject1, Subject2) 
                  OUTPUT inserted.ID INTO @InsertedIDs
                  VALUES (@Subj1, @Subj2);';
    
    -- 执行动态SQL并传入参数
    EXEC sp_executesql @CMDS,
                       N'@Subj1 INT, @Subj2 INT, @InsertedIDs TABLE(ID INT) OUTPUT',
                       @Subj1 = @Subject1,
                       @Subj2 = @Subject2,
                       @InsertedIDs = @InsertedIDs OUTPUT;
    
    -- 从表变量中提取ID
    SELECT @IDR = ID FROM @InsertedIDs;
END

方法2：将SCOPE_IDENTITY()写入动态SQL内部 #

把获取自增ID的逻辑直接放到动态SQL里，让它在同一作用域内给输出参数赋值：

ALTER PROC Experience
  @Subject1 INT
 ,@Subject2 INT
 ,@TableNumber INT
 ,@IDR INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @TableNames NVARCHAR(100);       
    SET @TableNames = N'TableNo_' + CONVERT(NVARCHAR(50), @TableNumber);
    
    DECLARE @CMDS NVARCHAR(MAX);           
    SET @CMDS = N'INSERT INTO ' + QUOTENAME(@TableNames) + N' (Subject1, Subject2) 
                  VALUES (@Subj1, @Subj2);
                  SET @IDR = SCOPE_IDENTITY();';
    
    -- 执行动态SQL并传递输出参数
    EXEC sp_executesql @CMDS,
                       N'@Subj1 INT, @Subj2 INT, @IDR INT OUTPUT',
                       @Subj1 = @Subject1,
                       @Subj2 = @Subject2,
                       @IDR = @IDR OUTPUT;
END

• 用QUOTENAME()包裹表名，避免表名含特殊字符时出错，同时提升安全性。
• 改用sp_executesql执行参数化动态SQL，彻底规避SQL注入风险，比直接字符串拼接安全得多。
• 加上SET NOCOUNT ON;，防止存储过程返回额外行数影响调用方逻辑。

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