Choose the minimum number in the range

Question

I have a table with data like.

ItemCode
1000
1002
1003
1020
1060

I am trying to write an 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 after the previous minimum order ID has been inserted into the table, but also skip the numbers that are already in the database. I want to get only one result each time the query is executed.

Thus, it should get 1001as the first result based on the table above. After it has ItemCode = 1001been inserted into the table, the next result that it should get should be 1004, because 1000before 1003already exists in the table.

Based on everything I've seen on the Internet, I think I need to use the While loop for this. Here is my code 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 that there should be an easier way to achieve this. Is there any way to tell me ...

select the minimum number from 1000 to 1060 that does not exist in table X

EDIT: creating a new table in my case is not an option

Final Edit: Thanks guys! I understood. Here is my last query that returns exactly what I want. I knew I was doing it too hard 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');

Answer 1

. , ( ) , 1, , , .

;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

Answer 2

, Tally. .

, @start @end. , Tally. . , , .

SQL Fiddle

DECLARE @start INT = 1000
DECLARE @end   INT = 1060

;WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP(@end - @start + 1) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1 FROM E4
)
SELECT
    MIN(@start + t.N)
FROM Tally t
WHERE
    @start + t.N <= @end
    AND NOT EXISTS(
        SELECT 1 
        FROM OITM
        WHERE CAST(ItemCode AS INT) = @start + t.N
    )

---

, sys.columns Tally:

;WITH Tally(N) AS(
    SELECT TOP(@end - @start + 1) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1 
    FROM sys.columns a
    --CROSS JOIN sys.columns b
)
SELECT
    MIN(@start + t.N)
FROM Tally t
WHERE
    @start + t.N <= @end
    AND NOT EXISTS(
        SELECT 1 
        FROM OITM
        WHERE CAST(ItemCode AS INT) = @start + t.N
    )

Answer 3

row_number() , , row_number() , . SQL Server , SQL Fiddle, , , , , - :

declare @lowerBound int = 1000;
declare @upperBound int = 1060;

declare @x table ([id] int);
insert @x values (1000), (1002), (1003), (1020), (1060);

with [SequenceCTE] as
(
    select
        [id],
        [seq] = (@lowerBound - 1) + row_number() over (order by [id])
    from
        @x
)
select top 1
    [seq]
from
    [SequenceCTE]
where
    [seq] != [id] and
    [seq] <= @upperBound;

EDIT: - SQL, . , . , - declare, , , , .