getting the definite last record ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

getting the definite last record ID

Author

Topic

Chief
Starting Member

3 Posts

Posted - 2004-02-24 : 16:05:12

This is an easy problem, but for some reason a little hard to explain typed out. So here's what I'm doing (MSSQL - not by choice...):

TableOne has an autonumber field called "ID"

$query1 = insert into TableOne (info) values (info);
$result1 = mssql_query($query2);

$query2 = select ID from TableOne order by ID desc;
$result2 = mssql_query($query2);
$last_id = mssql_fetch_result($result2,0,'ID');

$query3 = insert into TableTwo (fID, info) values ($last_id, info);
$result3 = mssql_query($query3);

The problem here, I believe, is that it's possible (not very... but this has to be definite) that a row could be inserted between $query2 and $query3 which would make the $last_id inaccurate...

Any suggestions would be appreciated.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 16:08:03

So you just want to ensure that you get the correct identity value into TableTwo?

If so, then you need to put SCOPE_IDENTITY() into a variable after the first insert that"

DECLARE @IdentValue INT

INSERT INTO TableOne(info) VALUES(@info)

SELECT @IdentValue = SCOPE_IDENTITY()

INSERT INTO TableTwo (fID, info) VALUES(@IdentValue, @info)

For more information, please read about SCOPE_IDENTITY() in SQL Server Books Online.

Tara

Chief
Starting Member

3 Posts

Posted - 2004-02-24 : 20:04:20

Thanks a bunch, I'm just dabbling in stored procs for the first time today - this helped a lot.

I have another question though, I'm having trouble returning values back to PHP from the stored proc. Example:

CREATE PROCEDURE GetID
@SPQuery text
AS
DECLARE @CurrentID INT
exec(@SPQuery)
SELECT @CurrentID = SCOPE_IDENTITY()
RETURN @CurrentID
GO

and the php

$stmt = mssql_init("GetID");
mssql_bind($stmt, "@SPQuery", $SPQuery, SQLVARCHAR);
$result = mssql_execute($stmt);

$data = mssql_fetch_row($result);

I get an error for the $data line saying: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 20:07:11

I don't have any experience on PHP.

BTW, your stored procedure is very bad for security reasons. You are allowing any query to be passed in. What if someone finds out about that stored procedure and passes DROP TABLE <name of most important table goes here> as @SPQuery. What you have written is highly not recommended. I never allow this kind of code in my environments nor will I ever.

Your stored proc should really be reconsidered.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources