What should an INSERT stored procedure do with the Identity value?

Question

I need to write a stored procedure to insert a row into a table. The table has an Identity column called ID. My question is should I return the value and if so, how?

As far as I can see,

• I could return it as the return value,
• I could return it as an out parameter, or
• I could do nothing with it.

What would a DB programmer do as a default?

Answer 1

"It depends."

Most of the time, I ignore it. Most callers don't need it. If you don't know your consumer, then I'd probably return it as a resultset (so you don't force the additional parameter on folks who don't need it.)

Answer 2

I prefer to return as return value if it is supposed to be used someday.

If it is useless, then I would not add extra complexity here.

Answer 3

I agree with Joe - it depends. The one advantage of returning it is that your calling app could use the presence of an ID as an indication of success.

Answer 4

Typically I will return the entire new row, which includes of course, the indentity column value. The reason I return the entire new row is because it's quite possible other columns got added to the row via triggers or other processes, and the calling application or process will find it useful to have the new row.

Answer 5

I usually add it as an out parameter and leave the return value to be 0 for success or non-0 for failure.

Answer 6

If you are looking for a One True Way Of Writing All Insert Procedures, then you're wasting your time. There will always be a table somewhere in your database that requires you to violate your own conventions somehow. The best you can hope for is a set of general consistency guidelines (not rules).

For general consistency guidelines, I suggest the following:

• The return value is always int and cannot be changed. Accordingly, it is best reserved for returning zero for success or @@error for failure.
• Output parameters are more efficient than the default resultset if you have a small collection of scalar values to send back. You can therefore return the @@identity value and any other auto-generated values (e.g. GUIDs, timestamps) in this manner.