Threadsafe generation of sequence numbers with stored procedures
Posted on 2003-03-24
I need to generate a sequence number for new entries in a table of Work Orders.
Each additional entry should get a unique sequence number.
The Sequence number should be a string with exactly seven characters.
Example: 0000001, 0000002, ... , 0001034, ...
The pocedure must be threadsafe,
so if multiple threads are calling the sql server at once it should still work reliably.
I would like to call the stored procedure from within vba.
It should work with MSSQL Server.
And it should be reasonably fast for large tables with about 100.000 entries.
It would be nice if the procedure could return the newly generated sequence number.
I think that's all.
Thanks for suggestions,