Bob,
Thanks for the quick input. It makes sense that one SP would solve the problem.
Is the where clause only necessary if you have more than one record in sy_uniqueid?
Is the from clause necessary to get an output variable which I assume is @KeyValue?
Also, thanks for all the good input over the years.
>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.
