問題描述

我有采用 @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.

這篇關于為什么 SCOPE_IDENTITY 返回 NULL?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！