Return value from stored procedure using scope_identity()

Question

I have a stored proc with two insert statements. I would like to return the ID from the first insert. It looks broadly like this:

declare @retVal int
Begin transaction

insert into myTable(..) values (..)

set @retVal = scope_identity()

insert into myTable2(..) values(..)

Commit transaction

return @retVal

When I do this, the insert statements work fine but the same value is returned every time (in this case, ID 6 from myTable, for some strange reason.)

Would anyone have a guess on what's going on?

@Thomas why do you think SELECT is better? You want to require all the scaffolding of retrieving a resultset, potentially using a DataReader, etc. to retrieve a single, scalar value? OUTPUT is far more appropriate in this case. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 7, 2013 at 18:50
Is the ID column on myTable an IDENTITY column or is the ID created/populated via a trigger? — Eric J. Price
– Eric J. Price, Commented Mar 7, 2013 at 19:13
Agreed output param is the right way to do it, but nothing syntactically wrong w/ return in his script. Definitely nothing that would return 6. Simply changing to select instead of return is unlikely to fix it. Need to see the full picture. — nathan_jr
– nathan_jr, Commented Mar 7, 2013 at 19:24
So, @larryq, is there a trigger on myTable? Could you share it? Could you also share the code where you are retrieving the return value and seeing 6 every time? — Aaron Bertrand
– Aaron Bertrand, Commented Mar 7, 2013 at 19:28

Aaron Bertrand · Accepted Answer · 2013-03-07 18:47:38Z

7

Please use an OUTPUT parameter. RETURN is for returning status/error codes, not data. For example, RETURN only handles INT. Guess what happens when you change your table to use BIGINT because INT wasn't enough?

ALTER PROCEDURE dbo.YourProcedure
  @params VARCHAR(32),
  @Table1ID INT = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  BEGIN TRANSACTION;

  INSERT dbo.Table1...
  SET @Table1ID = SCOPE_IDENTITY();
  INSERT dbo.Table2...

  COMMIT TRANSACTION;
END
GO

answered Mar 7, 2013 at 18:47

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

larryq Over a year ago

Using an output parameter did the trick, thanks very much to everyone. I'm happy to go that route from here on.

Prajwal Bhat Over a year ago

It returning both @Table1ID value and RETURN value... how to supress only the Table1ID ?

Aaron Bertrand Over a year ago

Sorry @bhat, I don't understand the issue. Maybe you should create a new question.

Collectives™ on Stack Overflow

Return value from stored procedure using scope_identity()

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related