Почему SCOPE_IDENTITY возвращает NULL?

Question

Я сохранил процедуру, которая принимает @dbName и вставьте запись в эту БД. Я хочу получить идентификатор последней вставленной записи. SCOPE_IDENTITY() возвращает NULL и @@IDENTITY возвращает правильный идентификатор, почему это происходит? Как я читал, так лучше использовать SCOPE_IDENTITY() на случай, если на столе есть триггеры. Могу ли я использовать IDENT_CURRENT? Возвращает ли он идентификатор в области таблицы, независимо от триггера?

Так в чем проблема и что использовать?

РЕДАКТИРОВАНИЕ

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

Answer 1

Как говорит Одед, проблема в том, что вы запрашиваете личность, прежде чем выполнить insert,

В качестве решения лучше всего запустить scope_identity как можно ближе к insert как вы можете. Используя 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 Data, показывающий, что scope_identity должен быть внутри sp_executesql,