If you retrieve and update the value in a single update statment, then you pretty much ensure that no two users can get the same value... here is the key pary of our get next key SP:

update sy_uniqueid
set
   last_id = last_id + @Increment,
   @KeyValue = last_id + @Increment
from
   sy_uniqueid
where
   tablename = @Name


And, I just recently learned this could be written like this too:


update sy_uniqueid
set
   @KeyValue = last_id = last_id + @Increment
from
   sy_uniqueid
where
   tablename = @Name



BOb


>In accounting the transaction table has many records with the same transaction number to identify those credits and debits that must balance.
>
>I need a stored proc that trys to lock if it can a table row holding the next transaction# so if it is not currently locked by someone else I can read, increment, write back the next higher integer.
>
>If it can't lock it must try again, (you get the picture);
>
>Does anyone have a well tested routine for this? Or what are people using for this kind of purpose?
>
>I could create a one to many relation and use the auto-incrementing id of the parent to fill the transactions of the children, but I haven't seen this used in accounting before.
>
>Any input is appreciated.