sql - Select minimum number in a range -

i have table data like.

itemcode 1000 1002 1003 1020 1060 

i'm trying write sql statement minimum number (itemcode) not in table , should able next lowest number once previous minimum order id has been inserted in table skip numbers in db. want 1 result each time query run.

so, should 1001 first result based on table above. once itemcode = 1001 has been inserted table, next result should should 1004 because 1000 1003 exist in table.

based on have seen online, think, have use while loop this. here code i'm still working on.

declare @count int set @count= 0    while exists (select itemcode                 oitm                 itemcode '10%'                 , convert(int,itemcode) >= '1000'                 , convert(int,itemcode) <= '1060')         begin             set @count = @count + 1              select min(itemcode) + @count             oitm             itemcode '10%'             , convert(int,itemcode) >= '1000'             , convert(int,itemcode) <= '1060'         end 

i feel there has easier way accomplish this. there way me say...

select minimum number between 1000 , 1060 doesn't exist in table x

edit: creating new table isn't option in case

final edit: guys! got it. here final query returns want. knew making complicated no reason!

with t0 ( select convert(int,itemcode) + row_number() on (order convert(int,itemcode)) itemcode              oitm              itemcode '10%'              , convert(int,itemcode) >= '1000'              , convert(int,itemcode) <= '1060') select min(convert(varchar,itemcode)) itemcode t0 convert(int,itemcode) not in (select convert(int,itemcode)                                     oitm                                     itemcode '10%'                                     , convert(int,itemcode) >= '1000'                                     , convert(int,itemcode) <= '1060'); 

this should thing. here generating sequantial number rows, comparing each row next row(done joining condition), , filtering rows difference not 1, ordering sequence , picking top most.

;with c as(select id, row_number() over(order id) rn) select top 1 c1.id + 1 newid c c1 join c c2 on c1.rn + 1 = c2.rn c2.id - c1.id <> 1 order c1.rn