0

On my web app people can create create what I call a case. On the back end side when they submit it saves it into a MSSQL DB table. After they hit save it'll create the new entry in the DB, what I need is to be able to retrieve the Idenity row (auto increment) which is my primary key so that I can save it in the session (PHP) for if the user hits save (to update) again.

My question, I know this is a common practice, what is the common way of doing so I was thinking about using SQL SCOPE_IDENTITY but was reading about common bugs here:

http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Thanks in advance

user1399840
  • 53
  • 1
  • 8
  • Can you verify what brand of database you're using, and perhaps add an appropriate tag? – Marvo Jun 14 '12 at 18:37

2 Answers2

1

If I understand your question correctly. I think this'll help.

http://php.net/manual/en/function.mysql-insert-id.php

Dev
  • 60
  • 1
  • 5
  • That's the kind of thing he needs, but unless that's a typo, he specified MSSQL (not MySQL) in his question. (A tag would be helpful.) – Marvo Jun 14 '12 at 18:37
  • Ahh... didn't read MSSQL. Apologies. Don't know enough about MSSQL to comment. – Dev Jun 14 '12 at 18:47
  • "OUTPUT Inserted.ID" it gives you the identity value back. And yes this is for MSSQL, not sure if this is standard. – user1399840 Jun 15 '12 at 18:54
1

Remove identity on primary column and Create you own custom identity generator function as

create function NextCustomerNumber() 
returns char(5) 
as 
begin 
    declare @lastval char(5) 
    set @lastval = (select max(myidentity) from mytable) 
    if @lastval is null set @lastval = 'C0001' 
    declare @i int 
    set @i = right(@lastval,4) + 1 
    return 'C' + right('000' + convert(varchar(10),@i),4) 
end
Raab
  • 34,778
  • 4
  • 50
  • 65
  • This is cool, thanks for the response, this is what I did: INSERT INTO Cases OUTPUT Inserted.ID VALUES (....) The whole addition is the "OUTPUT Inserted.ID" it gives you the identity value back. And yes this is for MSSQL, not sure if this is standard. – user1399840 Jun 15 '12 at 18:52