SQL Server Identity field

[tschick]

I am trying to do an insert into a SQL Server database that generates an autonumber and I need to return this number. How can I do an INSERT statement with a SELECT IDENT_CURRENT('Tablename') and return the autonumber? I need to include it in one statement to prevent possible data collisions. Any help would be appreciated. I am using Labview 6 and have the SQL toolit and database connectivity toolset.

[mikeporter]

Ok;

You can't insert and select data at the same time. I have a lot of experience with databases and there is always a way to get things done. What is the nature of the "data collisions" that you are wanting to avoid? Multiple systems inserting into the same table?

Mike...

[tschick]

Yes. I have multiple users writing to the same table and I need to return the record number that was generated by the INSERT statement. I can do an INSERT followed by the IDENT function in the same connection, but it is still possible for the wrong record number to be returned. I have been working with our IT department to set up a stored procedure to do this.

[mikeporter]

Yes, a stored procedure will certainly do what you need but it has the disadvantage of making you dependent upon support from IT if the SP doesn't work or needs to change. I can think of two simplier approaches:

1. Wrap the whole process in a transaction. That way each insert and ident operation should occur as an atomic process. Of course this assumes that SQL Server does everything the way it should--which is always a question.
2. Generate the primary key value in the application and don't depend upon the auto-generated keys. This would be my preferred approach.

In general, I prefer solutions that are not dependent upon any particular vendor's tools or features--like auto generated keys. Make your SQL as non-DBMS specific as you can and in the long run you'll have a lot fewer problems.

Alternately, you could move all your lower-level database access into an application server. In brief, an application server functions as an intermediate layer between your applications and the database. It's function is to hide from your code the specifics of exactly how the database communications takes place, and so isolate your code from changes in the database platform, structure or location. Such a structure benefits applications from a lot of different directions but is probibly beyond the scope of this answer. If you're interested contact me directly and I can fill you in on the details.

Mike...

[tschick]

Thanks Mike! I wasn't even thinking of Transactions. I tested it out and it works great. I agree about generating the value and sending it to the DB, but we can't change at this point. We are still planning on setting up a stored procedure at some point but this gives me some breathing room until that time.

Tim