David,

Using the UPDATE, would it be possible to have two or more users hit the routine at the same time and receive the same value? In VFP we would RLOCK the record to avoid this situation.

However, the identity column solution does sound like the better way to go.

Thanks,

Jerry



>Jerry,
>
>I'd recommend that you use an identity column if at all possible. If you can't you can accomplish what you want in an update:
>
>

>UPDATE KeyTable
>   SET @ReturnKey = LastKey = LastKey + 1
>   WHERE ...
>select @ReturnKey -- return to caller
>

>
>>Thanks, good point. Yes, I can make the order number field in the order header table an autoincrement. I was trying to duplicate what we currently do with VFP without an autoincrement, but you make a good point.