[SOLVED] Select minimum number in a range

Select minimum number in a range

I have a table with data like.

ItemCode
1000
1002
1003
1020
1060

I'm trying to write a SQL statement to get the minimum number (ItemCode) that is NOT in this table and it should be able to get the next lowest number once the previous minimum order ID has been inserted in the table but also skip the numbers that are already in the DB. I only want to get 1 result each time the query is run.

So, it should get 1001 as the first result based on the table above. Once the ItemCode = 1001 has been inserted into the table, the next result it should get should be 1004 because 1000 to 1003 already exist in the table.

Based on everything I have seen online, I think, I have to use a While loop to do this. Here is my code which I'm still working on.

DECLARE @Count int
SET @Count= 0   
WHILE Exists (Select ItemCode
                from OITM
                where itemCode like '10%'
                AND convert(int,ItemCode) >= '1000'
                and convert(int,ItemCode) <= '1060')
        Begin
            SET @COUNT = @COUNT + 1

            select MIN(ItemCode) + @Count
            from OITM
            where itemCode like '10%'
            AND convert(int,ItemCode) >= '1000'
            and convert(int,ItemCode) <= '1060'
        END

I feel like there has to be an easier way to accomplish this. Is there a way for me to say...

select the minimum number between 1000 and 1060 that doesn't exist in table X

EDIT: Creating a new table isn't an option in my case

Final Edit: Thanks guys! I got it. Here is my final query that returns exactly what I want. I knew I was making it too complicated for no reason!

With T0 as ( select convert(int,ItemCode) + row_number() over (order by convert(int,ItemCode)) as ItemCode
             from OITM
             where itemCode like '10%'
             AND convert(int,ItemCode) >= '1000'
             And convert(int,ItemCode) <= '1060')
Select MIN(convert(varchar,ItemCode)) as ItemCode
from T0
where convert(int,ItemCode) Not in (Select convert(int,ItemCode)
                                    from OITM
                                    where itemCode like '10%'
                                    AND convert(int,ItemCode) >= '1000'
                                    and convert(int,ItemCode) <= '1060');

Solution

This should do the thing. Here you are generating sequantial number for rows, then comparing each row with next row(done by joining condition), and filtering those rows only where difference is not 1, ordering by sequence and finally picking the top most.

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