为什么 SCOPE_IDENTITY 返回 NULL?

2021-09-10 00:00:00 sql tsql sql-server

我有采用 @dbName 并在该数据库中插入记录的存储过程.我想获取最后插入的记录的 id.SCOPE_IDENTITY() 返回NULL 并且@@IDENTITY 返回正确的id，为什么会发生?正如我读到的那样，最好使用 SCOPE_IDENTITY() 以防表上有一些触发器.我可以使用 IDENT_CURRENT 吗?是否返回表范围内的 id，与触发器无关?

I have stored procedure that take @dbName and insert record in that DB. I want to get the id of the last inserted record. SCOPE_IDENTITY() returns NULL and @@IDENTITY returns correct id, why it happens? As I read, so it's better to use SCOPE_IDENTITY() in case there are some triggers on the table. Can I use IDENT_CURRENT? Does it return the id in the scope of the table, regardless of trigger?

那么问题出在哪里，使用什么?

So what is the problem and what to use?

已编辑

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()

推荐答案

就像 Oded 所说的，问题是您在执行 insert 之前要求提供身份.

Like Oded says, the problem is that you're asking for the identity before you execute the insert.

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

As a solution, it's best to run scope_identity as close to the insert as you can. By using sp_executesql with an output parameter, you can run scope_identity as part of the dynamic SQL statement:

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 Data 中的示例，显示了 scope_identity 应该在 sp_executesql 内.

Here's an example at SE Data showing that scope_identity should be inside the sp_executesql.

相关文章