Use of SCOPE_IDENTITY()

[storm197]

Hi.

I'm using MS-SQL in my application and PHP.

I've been told to use SCOPE_IDENTITY() to get the the identity ID I just entered in a SQL query Insert.

Anyone know how to use this so I can use this identity in my PHP code ?

Thank you.

 

[ingresman]

You might be able to issue a select of the format

select scope_identity() as si

And extract the value of si into a string variable.
I can't try it as i don't have a sqlserver db at hand but it should only take a few mins of time to knock out a quick php script.
I would think as ling as the transaction remains open it should be ok.
Come back and tell us

 

[storm197]

Ok,

I found that Scope_identity is working only on SQL 2000 (I'm in 7.0)

What i'm using is the following query :

INSERT INTO tbl_cases (equipment_id, date_created, status, other_problem, owner, case_school, username, password, case_user_id)
SELECT
'$session_id_equipment',
'$now_date',
'1',
'$form_problem_description',
'$session_people_id_no',
'$session_assigned_school_id',
'$form_username',
'$form_password',
'$session_id_users'
SELECT @@IDENTITY AS 'case_ident'

I tried it and it works. But I don't know if it's reliable.

 

[ingresman]

I thought that @identity only works for integer columns.
If that is true (and your solution could be flakey) add a numeric identity column and use that.
Is 7.0 still supported ?