How to get identity value after calling exec(@Sql)

Question

I am trying to find the identity value of an inserted record inserted by exec(@Sql), but it seems that exec() excutes in a different scope.

/*
create table [dbo].[__Test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [description] [varchar](100) NULL
) ON [PRIMARY]
GO
*/

declare @Sql varchar(512)
set @Sql = 'insert into [dbo].[__Test] ([description]) values (''Some text'')'
exec (@Sql)

select
    @@IDENTITY as [@@IDENTITY],
    scope_identity() as [scope_identity],
    ident_current('__Test') as [ident_current]

/*  
if exists(select * from sys.objects where object_id = object_id('[dbo].[__Test]') and type='U')
    drop table [dbo].[__Test]
GO
*/

returns:

@@IDENTITY  scope_identity  ident_current
----------  --------------  -------------
14          NULL            14

and if there is a trigger on __Test, returns:

@@IDENTITY  scope_identity  ident_current
----------  --------------  -------------
6           NULL            14

So @@IDENTITY could be a trigger insert, the execution is not in scope and ident_current() could be from another user.

Is there any way of reliably finding the identity value from an insert made by exec()?

Answer 1

yes, by using sp_executesql:

DECLARE @nSQL NVARCHAR(500)
DECLARE @NewID INTEGER

SET @nSQL = 'INSERT MyTable (MyField) VALUES (123) SELECT @NewID = SCOPE_IDENTITY()'
EXECUTE sp_executesql @nSQL, N'@NewID INTEGER OUTPUT', @NewId OUTPUT

--@NewId now contains the ID

The advantage of sp_executesql is you can parameterise the SQL statement being executed, so you don't have to concentenate values into a string to then be executed.