Retrieving the auto-generated number
Posted on 2001-09-04
Actually I'm quite new to this, so your patience is much appreciated.
When inserting certain record that has a field as auto-generated number, one may need to return this number for later association with other records in other tables. For example; sometimes it's impossible to distinguish the record except from this number, so if inserted, there will be no way to get back to the record unless this auto-generated number is captured right after the insertion process.
In oracle, (I don't know really if it's oracle specific or not!) I found that they provide some sort of routine(maybe it's pl/sql, I don't know) on which you can invoke ".nextval" to insert the result as an ID to the record, then invoke ".currval" to get that ID for programming use. Frankly, I don't know if I can do such thing in MS SQL server or even how to do it, so I appreciate if you can help me in this.
I saw somewhere else that after insertion of certain record they do something like:
I really don't know how such thing works, or even if it could be used in MS SQL server, but I'm in favor of using such thing (or at least that what I feel from the syntax), because when dealing with concurrent invocations to database, one user may insert a record using
"recordnum.nextval" for example, and during insertion process, another may insert another record before the first has finished, so if the first one invokes
"recordnum.currval" he will take the wrong ID.
To brief, I have two questions:
1. Can we use [routine].nextval in MS SQL server? If so, is it safe to use it (see case above)? If yes, can you please tell me how [routine] can be created in MS SQL server.
2. Is there anything called "SELECT @@identity" in MS SQL server? Do I need special instructions when creating the database to be able to use it, like saying: "set this field as identity or auto number, etc."? Does it suffer from the concurrent invocation problem exists in case of [routine].nextval?
Thank you very much for your help.