48

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:

  1. As an output parameter SET @RETURN_VALUE = SCOPE_IDENTITY()
  2. As a scalar SELECT SCOPE_IDENTITY()
  3. Another way?

Any pros/cons to each?

Nelson Rothermel
  • 9,436
  • 8
  • 62
  • 81

5 Answers5

42

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):

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • Good example of both ways to do it, although I don't think I would do both at the same time unless I had strong reason to believe both were needed, for example, if I had no idea what language would be consuming my proc and knew it needed to support both methods. – John M Gant Jun 29 '10 at 17:16
  • True. I shouldn't have said, "usually". I've used both methods in an environment where we needed access to both forms of output. I'll reword my answer. – Tom H Jun 29 '10 at 17:31
39

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

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.

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.

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

Misiu
  • 4,738
  • 21
  • 94
  • 198
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Good point on returning multiple rows if there were multiple inserts. Maybe gbn beat you to that, but yours was more thorough. In my particular case I'm not using an ORM and (for now) won't be inserting multiple records at once. – Nelson Rothermel Jun 29 '10 at 21:15
  • 1
    @Remus: you said, "Overall, I can't see a single case when returning SCOPE_IDENTITY() as a result set would be a good practice.". Then how else could it be done? Are you implying the overall use of SCOPE_IDENTITY() is bad practice? In what sense please? – Fandango68 May 23 '16 at 02:01
  • 4
    @Fernando68: I'm saying you should get it as an output parameter, not as a result set – Remus Rusanu May 23 '16 at 07:42
  • Yes I agree. I thought that's what you meant. Thanks – Fandango68 May 24 '16 at 02:27
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.

TLiebe
  • 7,913
  • 1
  • 23
  • 28
  • For what it's worth, the same reasoning could be applied to either option 1 or 2, that if you need to add another output value, you could either add a new parameter under option 1 or add another field to your select (or another select if your consumer supports it) under option 2. I prefer option 1 myself, based on an unsubstantiated supposition that it requires less "overhead" on the part of the database and/or the client. – John M Gant Jun 29 '10 at 17:24
  • I'm with jmgant on this. It's not that hard to `SELECT` an additional column. I know you can make input parameters optional (with a default value); what about output parameters? If you can't, it's easier to make changes to the `SELECT` since you'll probably ignore the extra column unless you need it. In spite of that, I think the output parameter is the "proper" way without the overhead of a result set. – Nelson Rothermel Jun 29 '10 at 21:10
2
SELECT IDENT_CURRENT('databasename.dbo.tablename') AS your identity column;
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
billah77
  • 196
  • 1
  • 1
  • 13
  • 6
    Please edit with more information. Code-only and "try this" answers are [discouraged](http://meta.stackexchange.com/questions/196187/is-try-this-bad-practice), because they contain no searchable content, and don't explain why someone should "try this". – Rick Smith Sep 15 '15 at 15:35
2

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

Return values would normally be used for error handling.

gbn
  • 422,506
  • 82
  • 585
  • 676