Stored procedure - return identity as output parameter or scalar

Solution 1:

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.

Solution 2:

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.