title: How to get identity value after calling exec(@Sql)
body: <p>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.</p>
  
  <pre><code>/*
  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
  */
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>@@IDENTITY  scope_identity  ident_current
  ----------  --------------  -------------
  14          NULL            14
  </code></pre>
  
  <p>and if there is a trigger on __Test, returns:</p>
  
  <pre><code>@@IDENTITY  scope_identity  ident_current
  ----------  --------------  -------------
  6           NULL            14
  </code></pre>
  
  <p>So @@IDENTITY could be a trigger insert, the execution is not in scope and ident_current() could be from another user.</p>
  
  <p>Is there any way of reliably finding the identity value from an insert made by exec()?</p>