如何在调用 exec(@Sql) 后获取身份值

Question

我试图找到 exec(@Sql) 插入的插入记录的标识值，但 exec() 似乎在不同的范围内执行。

/*
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
*/

返回：

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

如果 __Test 上有触发器，则返回：

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

因此 @@IDENTITY 可能是触发器插入，执行不在范围内，并且 ident_current() 可能来自另一个用户。

有没有什么方法可以从 exec() 所做的插入中可靠地找到标识值？

Answer 1

是的，通过使用 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