Stored procedure - return identity as output parameter or scalar

Question

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?

Answer 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.