CODEWITHSUNDEEP
sql-serverstored-proceduresreturn-valueidentity
ssg31415926
ssg31415926

Reputation: 1077

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

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,

What would a DB programmer do as a default?

Upvotes: 0

Views: 831

Answers (6)

Christian Hayter
Christian Hayter

Reputation: 31071

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.

Upvotes: 2

erikkallen
erikkallen

Reputation: 34401

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

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48402

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.

Upvotes: 2

Bob Palmer
Bob Palmer

Reputation: 4762

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.

Upvotes: 2

BarsMonster
BarsMonster

Reputation: 6585

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.

Upvotes: 2

Joe
Joe

Reputation: 42627

"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.)

Upvotes: 3

Related Questions