I have a table called Operators, for some obscure reason that defies logic, a numeric field called OperatorID of type Long Integer has been throttled by a business policy, stating that "there shall never be more than 100 operators" hmmmm.
The throttling is in the form of an input mask '00' and a format of '00' being applied at table level indexed(no duplicates).
The Problem is this:
I need to assign a new OperatorID, now operatorID's 01, 02 and 03 have been assigned, as have 99, 98, 97.
I need some SQL script that will return the first available number in the range 1 - 100 that has not been consumed.
Hope that is clear enough for you, if not, let me know, and I will attempt to clarify.
Dont really want to use a function(builtin or custom) for this if at all possible, want it in SQL script please.