存储过程 - 返回身份作为输出参数或标量 [英] Stored procedure - return identity as output parameter or scalar
问题描述
当您将记录插入具有标识列的表中时，您可以使用 SCOPE_IDENTITY() 来获取该值.在存储过程的上下文中，这是返回标识值的推荐方式:
When you insert a record into a table with an identity column, you can use SCOPE_IDENTITY() to get that value. Within the context of a stored procedure, which would be the recommended way to return the identity value:
- 作为输出参数
SET @RETURN_VALUE = SCOPE_IDENTITY() - 作为标量
SELECT SCOPE_IDENTITY() - 另一种方式?
每种方法有什么优点/缺点吗?
Any pros/cons to each?
推荐答案
这一切都取决于您的客户端数据访问层.许多 ORM 框架依赖于在插入操作期间显式查询 SCOPE_IDENTITY.
Its all depend on your client data access-layer. Many ORM frameworks rely on explicitly querying the SCOPE_IDENTITY during the insert operation.
如果您可以完全控制数据访问层，则可以说将 SCOPE_IDENTITY() 作为输出参数返回更好.将返回结果包装在结果集中会增加不必要的元数据开销来描述结果集，并使处理请求结果的代码变得复杂.
If you are in complete control over the data access layer then is arguably better to return SCOPE_IDENTITY() as an output parameter. Wrapping the return in a result set adds unnecessary meta data overhead to describe the result set, and complicates the code to process the requests result.
如果您更喜欢结果集返回，那么再次使用 OUTPUT 子句是有争议的:
If you prefer a result set return, then again is arguable better to use the OUTPUT clause:
INSERT INTO MyTable (col1, col2, col3)
OUTPUT INSERTED.id, col1, col2, col3
VALUES (@col1, @col2, @col3);
通过这种方式，您可以返回整个插入的行，包括默认列和计算列，并且您会得到一个结果集，其中插入的每一行包含一行，这与面向集合的批量插入一起工作正常.
This way you can get the entire inserted row back, including default and computed columns, and you get a result set containing one row for each row inserted, this working correctly with set oriented batch inserts.
总的来说，当返回 SCOPE_IDENTITY() 作为结果集时，我看不到一个案例，这是一个很好的做法.
Overall, I can't see a single case when returning SCOPE_IDENTITY() as a result set would be a good practice.
这篇关于存储过程 - 返回身份作为输出参数或标量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
