How to get id value after exec call (@Sql)

Question

I am trying to find the id value of the inserted record inserted by exec (@Sql), but it seems that exec () is being calculated in another area.

/*
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 in __Test, it returns:

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

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

Is there any way to reliably find the identifier value from the insert made by exec ()?

Answer 1

yes 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

sp_executesql , SQL, , .

Answer 2

, sp_executesql , , .

EXEC :

DECLARE @Result AS Table (Value int)
INSERT INTO @Result EXEC('INSERT INTO MyTable(Fields) ''Value''; SELECT @@IDENTITY')
SELECT Value FROM @Result