T-SQL EXEC and scope

Question

Let's say I have a stored procedure with this in its body:

EXEC 'INSERT INTO ' + quotename(@table) ' blah...'
SELECT IDENT_CURRENT('' + @table + '')

Is IDENT_CURRENT() guaranteed to get the identity of that row INSERTed in the EXEC? IDENT_CURRENT() "returns the last identity value generated for a specific table in any session and any scope", but the scope is different within the EXEC than the stored procedure, right?

I want to make sure that if the stored procedure is being called multiple times at once, the correct identity is SELECTed.

EDIT: Or do I need to do both the INSERT and SELECT within the EXEC, like so:

declare @insert nvarchar
set @insert = 
    'INSERT INTO ' + quotename(@table) ' blah...' +
    'SELECT IDENT_CURRENT(''' + @table + ''')'
EXEC @insert

And if that's the case, how do I SELECT the result of the EXEC if I want to continue with more code in T-SQL? Like this (although it's obviously not correct):

declare @insert nvarchar
set @insert = 
    'INSERT INTO ' + quotename(@table) ' blah...' +
    'SELECT IDENT_CURRENT(''' + @table + ''')'

declare @ident int
set @ident = EXEC @insert

-- more code
SELECT * FROM blah

UPDATE: In the very first snippet, if I SELECT SCOPE_IDENTITY() instead of using IDENT_CURRENT(), NULL is returned by the SELECT. :(

Answer 1

According to Microsoft's T-SQL docs:

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

So I would say, no, IDENT_CURRENT does not guarantee to give you back the right value. It could be the last IDENTITY value inserted in a different session.

I would make sure to use SCOPE_IDENTITY instead - that should work reliably.

Marc

Answer 2

I think Scope_Identity() is what you're looking for, which will give you the most recent identify in the current scope.

Answer 3

I'd like to chip in my favourite solution by using OUTPUT keyword. Since INSERT can support multiple rows at a time, we would want to know the identities inserted. Here goes:

 
-- source table
if object_id('Source') is not null drop table Source
create table Source
(
    Value datetime
)
-- populate source
insert Source select getdate()
waitfor delay '00:00.1'
insert Source select getdate()
waitfor delay '00:00.1'
insert Source select getdate()
select * from Source -- test
-- destination table
if object_id('Destination') is null
create table Destination
(
    Id int identity(1, 1),
    Value datetime
)
-- tracking table to keep all generated Id by insertion of table Destination
if object_id('tempdb..#Track') is null
create table #Track 
(
    Id int  
)
else delete #Track
-- copy source into destination, track the Id using OUTPUT
insert Destination output inserted.Id into #Track select Value from Source
select Id from #Track -- list out all generated Ids

Go ahead to run this multiple times to feel how it works.

Answer 4

Try

EXEC 'INSERT INTO ' + quotename(@table) ' blah...; SELECT @@IDENTITY'

or better, according to this

EXEC 'INSERT INTO ' + quotename(@table) ' blah...; SELECT SCOPE_IDENTITY()'

Answer 5

http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

There is a bug in SCOPE_IDENTITY() I have switched my stored procedures over to the methodology used to retrieve default values from an insert:

 declare @TheNewIds table (Id bigint, Guid uniqueidentifier)
 insert [dbo].[TestTable] output inserted.Id, inserted.Guid into @TheNewIds
 values (default);
 select @Id = [Id], @Guid = [Guid] from @TheNewIds;