Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

environment sql server 2005

In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the original stored proc. I am doing this by using return scope_identity(); My understanding is return is only used for error messages/status codes. Is this a "good" way of doing what I need to do, or is there another "better" way?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

All procedures have an integer return value. Your method would work, there is no hard and fast rule against it.

Alternatively, you could return an output parameter.

Example:

create procedure YourProcedure
  @ID output
as
  insert into YourTable (bunch of values)
  select @ID = scope_identity()
go

You then call your procedure as follows:

declare @TheID int
exec YourProcedure @TheID output
select @TheID
share|improve this answer

There are 3 ways:

  • OUTPUT parameter (as per datagod's answer)
  • RETURN (as you noted)
  • A simple SELECT which can be consumed say with ExecuteScalar

I'd usually use an OUTPUT parameter because it's the lightest way.

Note: If you are inserting several rows into one table and only getting the last IDENTITY there is no guarantee that the previous rows are contiguous

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.