为什么SCOPE_IDENTITY返回NULL？

Question

我有一个存储过程，使用@dbName将记录插入到该数据库中。我想获取最后插入的记录ID。SCOPE_IDENTITY()返回NULL，@@IDENTITY返回正确的ID，为什么会发生这种情况？根据我的阅读，如果表上有触发器，则最好使用SCOPE_IDENTITY()。

我能否使用IDENT_CURRENT？它是否返回表作用域内的ID，而不管触发器？

所以问题是什么以及应该使用哪个方法？

DECALRE @dbName nvarchar(50) = 'Site'
DECLARE @newId int
DECLARE @sql nvarchar(max)
SET @sql = N'INSERT INTO ' + quotename(@dbName) + N'..myTbl(IsDefault) ' +
           N'VALUES(0)'     
EXEC sp_executesql @sql

SET @newId = SCOPE_IDENTITY()

- theateist

---

关于我为什么给你的问题点了踩，我想说几句。从下面的评论中可以看出，你在回答被提供之后编辑了你的问题，这让回答看起来很混乱。不知道如何做某事并没有什么可耻的。公开你的学习过程，让其他人也能从中学习。现在，有同样问题的人将无法从你的经验中学习到东西。 - Ben Thul

@Ben，我不同意你的观点。这与“...不知道是可耻的...”无关。我在回答Martin时解释了为什么我编辑了我的帖子。我错误地将它们放在了错误的位置。但在我写代码（在家里）时，我把它们放在了正确的位置。请投票支持我的问题。 - theateist

Answer 1

就像Oded所说的那样，问题在于您在执行 insert 之前请求身份。

作为解决方案，最好在尽可能靠近 insert 的位置运行 scope_identity 。 通过使用带有输出参数的 sp_executesql ，您可以将 scope_identity 作为动态SQL语句的一部分运行：

SET @sql = N'INSERT INTO ' + quotename(@dbName) + N'..myTbl(IsDefault) ' +
           N'VALUES(0) ' +
           N'SET @newId = SCOPE_IDENTITY()'
EXEC sp_executesql @sql, N'@newId int output', @newId output

这里有一个从 SE 数据库获取的示例，说明 scope_identity 应该在 sp_executesql 内部使用。

- Andomar

---

问题在于不同的作用域，而不是操作顺序。执行插入的 sp_executesql 在 SCOPE_IDENTITY() 调用之前执行（编辑：我看到 OP 现在已经编辑了他们的问题，使得你的第一行出现错误 +1）。 - Martin Smith

@Martin，我错误地将它们写成了错误的顺序。在我的代码中，我已经按正确的顺序编写了它们。无论如何，我已经编辑了我的帖子。 - theateist

@Andomar，我编辑了我的代码。为什么我需要在@sql中插入SCOPE_IDENTITY？为什么我不能在sp_executesql之后调用它？ - theateist

sp_executesql内部的代码运行在不同的"作用域"中。（不要尝试使用@@identity来解决这个问题，虽然它可以解决此问题，但会留下一堆其他问题。） - Andomar

SQL Server有时会重新排序操作吗？我遇到了一个间歇性（且罕见）的情况，即在插入后立即从SCOPE_IDENTITY()获取null... - Brian Knoblauch

@BrianKnoblauch：如果在作用域中还没有对标识列进行任何插入操作，那么SCOPE_IDENTITY()函数将返回null值。 - Andomar