INSERT 存储过程应该如何处理 Identity 值？
我需要编写一个存储过程来将一行插入表中。该表有一个称为 ID 的标识列。我的问题是我应该返回该值吗？如果是，如何返回？
据我所知，
- 我可以将它作为返回值返回，
- 我可以将它作为输出参数返回，或者
- 我不能用它做任何事情。
数据库程序员默认会做什么？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
“这取决于。”
大多数时候，我都会忽略它。大多数呼叫者不需要它。如果您不了解您的消费者，那么我可能会将其作为结果集返回（这样您就不会将附加参数强加给不需要它的人。）
"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.)
如果您正在寻找一种编写所有插入过程的真正方法，那么您就是在浪费时间。数据库中的某处总会有一个表要求您以某种方式违反自己的约定。您所能期望的最好结果是一套通用的一致性准则（而不是规则）。
对于一般一致性准则，我建议如下：
int并且无法更改。因此，最好保留它以表示成功返回零，或表示失败返回@@error。@@identity值和任何其他自动生成的值（例如GUID、时间戳）。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:
intand cannot be changed. Accordingly, it is best reserved for returning zero for success or@@errorfor failure.@@identityvalue and any other auto-generated values (e.g. GUIDs, timestamps) in this manner.如果有一天应该使用它，我更愿意作为返回值返回。
如果没有用，那么我就不会在这里增加额外的复杂性。
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.
我同意乔的观点——这要看情况。返回它的一个优点是您的调用应用程序可以使用 ID 的存在作为成功的指示。
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.
通常我会返回整个新行，其中当然包括 indentity 列值。我返回整个新行的原因是因为其他列很可能通过触发器或其他进程添加到该行，并且调用应用程序或进程会发现拥有新行很有用。
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.
我通常将其添加为输出参数，并将返回值保留为 0 表示成功，保留非 0 表示失败。
I usually add it as an out parameter and leave the return value to be 0 for success or non-0 for failure.