如何在SQL存储过程中通过SCOPE_IDENTITY获取新插入的ID？

SCOPE_IDENTITY()返回当前作用域内最后插入的自增ID，而@@IDENTITY会跨作用域返回触发器中生成的ID；前者作用域隔离、会话安全，后者易受触发器干扰导致取错值。

SCOPE_IDENTITY() 返回什么，和 @@IDENTITY 有什么区别？

它只返回当前作用域（比如当前存储过程、当前批处理）中最后一条 INSERT 语句生成的自增 ID。这是关键——如果触发器里也插了数据，@@IDENTITY 会返回触发器里的 ID，而 SCOPE_IDENTITY() 不会。

常见错误现象：SELECT @@IDENTITY 在有触发器的表上总拿错 ID；或者在动态 SQL 后直接调用，结果返回 NULL 或旧值。

• 必须紧跟在 INSERT 语句之后立即调用，中间不能有其他语句（包括 PRINT、SET 变量赋值等）
• 不能跨批：如果用 EXEC('INSERT ...')，那 SCOPE_IDENTITY() 拿不到这个 ID，因为动态 SQL 是独立作用域
• 只对 IDENTITY 列有效，对 SEQUENCE 或 NEWID() 无效

在存储过程中正确获取并返回新插入 ID 的写法

最稳妥的方式是把 SCOPE_IDENTITY() 结果赋给一个变量，并立刻检查是否为 NULL（说明没插入成功或目标表无 IDENTITY 列）。

CREATE PROCEDURE InsertUser    @Name NVARCHAR(50),    @Email NVARCHAR(100),    @NewId INT OUTPUTASBEGIN    INSERT INTO Users (Name, Email) VALUES (@Name, @Email);<pre class="brush:php;toolbar:false;">-- 紧跟其后，不加任何中间语句SET @NewId = SCOPE_IDENTITY();-- 必须检查！否则调用方可能拿到 NULL 却误以为成功IF @NewId IS NULL    THROW 50000, 'Failed to retrieve inserted ID', 1;

END

• 输出参数 @NewId 是最常用且可控的返回方式，比 SELECT 更适合被其他过程调用
• 避免用 SELECT SCOPE_IDENTITY() 直接返回结果集，容易被客户端误解析为多结果集
• 如果插入失败（如违反约束），SCOPE_IDENTITY() 仍返回前一次成功插入的 ID —— 所以一定要结合插入是否成功来判断

为什么不能在 INSERT 后加 BEGIN...END 或其他逻辑再取值？

只要作用域没变，SCOPE_IDENTITY() 就始终指向“本作用域内最后一次 INSERT”。但加了 BEGIN...END 并不会改变作用域；真正危险的是中间穿插了另一条 INSERT（哪怕只是日志表）。

• 错误示例：INSERT INTO Users...; INSERT INTO LogTable...; SELECT SCOPE_IDENTITY(); → 返回的是 LogTable 的 ID
• 安全做法：把所有需要的插入操作拆开，每次插入后立刻取 ID，或用临时表/表变量暂存
• 如果必须批量插入多行又想拿到全部 ID，OUTPUT 子句比 SCOPE_IDENTITY() 更合适（它能返回每行的 IDENTITY 值）

兼容性和替代方案提醒

SCOPE_IDENTITY() 在 SQL Server 2000+ 全系列支持，但不适用于 Azure SQL 托管实例以外的其他云数据库（比如 PostgreSQL 用 RETURNING，MySQL 用 LAST_INSERT_ID()）。

• SQL Server 2005+ 推荐优先用 OUTPUT：例如 INSERT INTO Users (...) OUTPUT INSERTED.Id VALUES (...)
• 如果表没有 IDENTITY 列，但用了 SEQUENCE，就得改用 NEXT VALUE FOR seq_name 预取，再传入 INSERT
• 在高并发场景下，SCOPE_IDENTITY() 是线程安全的，不用担心被其他连接干扰 —— 这点比 @@IDENTITY 强得多

最容易被忽略的一点：很多人以为只要写了 INSERT 就一定有 ID 可取，却忘了检查表结构是否真有 IDENTITY 列，或者是否被禁用了 IDENTITY_INSERT。