We have a situation where we store a one column, one row entry called CurrTransNo. As the name implies, it stores the next transaction number to be used. This transaction number will be used across many different transaction tables, so it must be unique across all of the tables.

We're trying to come up with the safest way to avoid any concurrency issues and to avoid any instance of duplicate transaction records. We have a stored procedure that increments the NextTransNo by one, and then returns the new number as a result, so it can be used, like so...

DECLARE @nNextTransNo int

UPDATE NextTransTable SET @nNextTransNo = CurrTransNo + 1, CurrTransNo = CurrTransNo + 1 FROM NextTransTable

SELECT @nNextTransNo

GO

Short of implementing locks in SQL...since the setting of the variable and the updating of the record are done at what appears to be the same time - can anyone think of a better way to handle this?

Thanks,
Kevin