问题描述
我有采用 @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.