T-SQL EXEC和范围 [英] T-SQL EXEC and scope

问题描述

假设我的主体中有一个存储过程:

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

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

IDENT_CURRENT()是否可以保证在EXEC中插入该行的标识? IDENT_CURRENT()返回在任何会话和任何作用域中为特定表生成的最后一个标识值"，但是EXEC中的作用域与存储过程不同，对吗?

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.

还是我需要在EXEC中同时执行INSERT和SELECT?

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

如果是这样，如果我想继续使用T-SQL中的更多代码，该如何选择EXEC的结果?像这样(尽管这显然是不正确的):

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

更新:在第一个代码段中，如果我选择SCOPE_IDENTITY()而不是使用IDENT_CURRENT()，则SELECT返回NULL. :(

推荐答案

尝试

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

或更好，根据此

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

这篇关于T-SQL EXEC和范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！