Why SCOPE_IDENTITY returns NULL?

Question

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?

EDITED

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

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

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

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