@@IDENTITY -> session variable help

yoda8028

Hello all, I am having this problem with the @@Identity function.

Let me explain the situation. I have a sql server 2000 database which has a table with a field that is primary key auto increment. After I enter the first table information I wish to return the auto increment (primary key) from that table and make it a session variable to pass from page to page. Can anyone help? 🙂

database name: Survey
table name: survey
auto increment field: surveyid

yoda8028

anyone, anyone.... bueller? bueller?
any help will be very appreciated. 🙂

yoda8028

someone please help me 🙁

AstroTeg

I've done this in ASP with SQL v7 and 2000.

In ASP, I couldn't just run my insert query and then run a 2nd query to select @@IDENTITY (which is what you need to do, I believe - I haven't looked at the PHP MS SQL functions).

What I had to do was execute (not query) the insert query, then execute the 2nd query to fetch the @@IDENTITY ("SELECT @@IDENTITY AS myID").

I'm just guessing here. Check out the PHP MS SQL functions and see if: there's a function to grab the ID. If not, then check if there's a function which does an execute instead of "query". If so, use it to do the insert and then do the 2nd select query.

tomhath

@@Identity isn't a function, it's a global variable.

And it isn't specific to a table, it's the most recently inserted autoincrement value from the current connection, regardless of what table you inserted into.

So all you need to do is query it and fetch the row:

select @@Identity as Id