存储过程——返回作为输出参数或标量的标识

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:

当您将一个记录插入一个具有标识列的表中时，您可以使用SCOPE_IDENTITY()来获取该值。在存储过程的上下文中，这是返回标识值的推荐方法:

As an output parameter SET @RETURN_VALUE = SCOPE_IDENTITY()
作为输出参数集@RETURN_VALUE = SCOPE_IDENTITY()
As a scalar SELECT SCOPE_IDENTITY()
作为标量选择SCOPE_IDENTITY()
Another way?
另一种方式?

Any pros/cons to each?

每一个的优点/缺点吗?

5 个解决方案

#1

Its all depend on your client data access-layer. Many ORM frameworks rely on explicitly querying the SCOPE_IDENTITY during the insert operation.

这一切都取决于客户端数据访问层。许多ORM框架都依赖于在插入操作期间显式查询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.

如果您完全控制了数据访问层，那么返回SCOPE_IDENTITY()作为输出参数可能更好。在结果集中包装返回会增加不必要的元数据开销来描述结果集，并使处理请求结果的代码变得复杂。

If you prefer a result set return, then again is arguable better to use the OUTPUT clause:

如果您更喜欢结果集返回，那么使用OUTPUT子句还是有争议的:

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.

通过这种方式，您可以获得整个插入的行，包括默认的和计算的列，并且您可以获得一个结果集，其中包含每个插入行的一行，这个结果集可以正确地使用集合定向的批插入。

Overall, I can't see a single case when returning SCOPE_IDENTITY() as a result set would be a good practice.

总的来说，当返回SCOPE_IDENTITY()作为结果集时，我无法看到单个情况，这将是一个很好的实践。

#2

Another option would be as the return value for the stored procedure (I don't suggest this though, as that's usually best for error values).

另一个选项是作为存储过程的返回值(尽管我不建议这样做，因为这对于错误值来说通常是最好的)。

I've included it as both when it's inserting a single row in cases where the stored procedure was being consumed by both other SQL procedures and a front-end which couldn't work with OUTPUT parameters (IBATIS in .NET I believe):

当它插入一行时，如果存储过程被其他SQL过程消耗，而前端无法使用输出参数(我相信。net中的IBATIS)，我将它包含在这两种情况中:

CREATE PROCEDURE My_Insert
    @col1            VARCHAR(20),
    @new_identity    INT    OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO My_Table (col1)
    VALUES (@col1)

    SELECT @new_identity = SCOPE_IDENTITY()

    SELECT @new_identity AS id

    RETURN
END

The output parameter is easier to work with in T-SQL when calling from other stored procedures IMO, but some programming languages have poor or no support for output parameters and work better with result sets.

当从其他存储过程IMO调用时，输出参数在T-SQL中更容易使用，但是一些编程语言对输出参数的支持很差，或者不支持，并且在结果集上工作得更好。

#3

I prefer to return the identity value as an output parameter. The result of the SP should indicate whether it succeeded or not. A value of 0 indicates the SP successfully completed, a non-zero value indicates an error. Also, if you ever need to make a change and return an additional value from the SP you don't need to make any changes other than adding an additional output parameter.

我宁愿返回标识值作为输出参数。SP的结果应该表明它是否成功。值0表示SP成功完成，非0表示错误。此外，如果您需要进行更改并从SP返回额外的值，那么除了添加额外的输出参数之外，您不需要进行任何更改。

#4

Either as recordset or output parameter. The latter has less overhead and I'd tend to use that rather than a single column/row recordset.

作为记录集或输出参数。后者的开销更少，我倾向于使用它而不是单个列/行记录集。

If I expected to >1 row I'd use the OUTPUT clause and a recordset

如果我期望>一行，我会使用OUTPUT子句和记录集

Return values would normally be used for error handling.

返回值通常用于错误处理。

#5

SELECT IDENT_CURRENT('databasename.dbo.tablename') AS your identity column;

#1