>Is the where clause only necessary if you have more than one record in sy_uniqueid?

Nope, we have a record for each table in our database to serve up primary keys, hence the where.

>Is the from clause necessary to get an output variable which I assume is @KeyValue?

You know what, you may not [probably don't] need it. I am just so used to putting it cause we usually need to join to another table when we do updates so I am used to putting it there.

>Also, thanks for all the good input over the years.

No problem, it's the giving back thing... I am pretty much in "giving back" mode for VFP and SQL and "takeing" mode for .Net. I hope to be in "giving back" mode for .Net in a few years.

BOb

>
>>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.