Katnoodle: by Richard Cassidy » Blog Archive » mssql last inserted id thing

mssql last inserted id thing

Posted Monday, August 20th, 2007 at 10:41 pm by Richard in the stuff, geek, web category.

I’m currently doing some fantastically exciting (!) web work for a large national company who have the pleasure of using mssql – poor people.

Mssql is like some horrible, deformed half cousin of MySQL. It doesn’t have lots of useful features like LIMIT and so on.

But one of the most annoying is the lack of mysql_insert_id() functionality.

So with a bit of help from my rather more talented friend, Daniel, I present …
function mssql_insert_id($q,$link) { // return the id of the inserted record // $q is the SQL INSERT query // $link is the db link resource


  $q2="SELECT SCOPE_IDENTITY() as idno;";
  $sqlTxt="exec(\"".$q."\n".$q2."\")";

if ($dbResult=mssql_query($sqlTxt,$link)) {
  $res=mssql_fetch_array($dbResult);
  return($res<sup><a href="#fn0">0</a></sup>);
  }
  return('');

}

Just pass it the normal INSERT query you would run, and the db link resource and it will insert the record and pass back the id. It’s the exec bit that is important as without that both SCOPE_IDENTITY() and @@IDENTITY return null values.

Not too shabby even if I do say so myself.

You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

Comments...

Ben says... [toggle display]

You should use SELECT SCOPE_IDENTITY() which limits the scope to the insertion you have just done. Using @@IDENTITY can return an incorrect value if you have triggers on the table as it returns the last-inserted identity value- which could be the identity of an inserted value from a trigger.

This is only available from SQL2k onwards.

Ben

written on August 21st, 2007 at 10:09 am

Reply to this comment

Richard says... [toggle display]

You’re right of course – thanks for the tip.

written on August 22nd, 2007 at 11:06 am

Reply to this comment