How can i get the first free ID from table

Question

I have a table with a primary key field ID. I don't want to use Identity because i need to give the User the posibility of manualy choose an ID for the new object. So my idea is:

1. By default in the edit view, the ID field will be 0.
2. If user don't change it, i need to find the first free ID and use it.
3. If the user change the ID, i first need to check if there's another object with that id, and in that case throw an error.
4. If not, use the ID choose by user.
5. create the new object

The question is how to query and SQL Server table to get the firts free ID number?

Examples 1:

ID
--
1
2
10

First free ID is 3

Examples 2:

ID
--
1
2
3
4

First free ID is 5

Is there a way to do that? All i can think of is get the min and max value, create a cycle for possible values and then compare with table data, but it involves too many querys to the database. Thanks!

Answer 1

You can find the first free id as the first id where there is no "next" value:

select coalesce(min(t.id) + 1, 0)
from table t left outer join
     table t2
     on t.id = t2.id - 1
where t2.id is null;

EDIT:

If you want to handle "1" as a potential missing value:

select (case when min(minid) > 1 then 1 else coalesce(min(t.id) + 1, 0) end)
from table t left outer join
     table t2
     on t.id = t2.id - 1 cross join
     (select min(id) as minid from table t) const
where t2.id is null;

Answer 2

Test Table

CREATE TABLE ID_TABLE(ID INT)
INSERT INTO ID_TABLE VALUES
(1),(2),(10)

Stored Procedure

ALTER PROCEDURE dbo.usp_GetNextValue
@nxt_ID_Wanted INT = 0,
@nxt_ID_Available INT  OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- If user hasnt passed any value get next avilable value
IF (@nxt_ID_Wanted = 0)   
 BEGIN 
          SELECT TOP 1 @nxt_ID_Available  =  ID + 1 
        FROM 
         (
          SELECT   ID , ROW_NUMBER() OVER (ORDER BY ID ASC) AS rn
         FROM ID_TABLE
         )Q
        WHERE ID = rn
        ORDER BY ID DESC

      IF (@nxt_ID_Available IS NULL)
        BEGIN
          SET @nxt_ID_Available = 1;
        END
 END
     -- If user has passed a value check if it exists and raise error
ELSE  IF EXISTS(SELECT 1 FROM ID_TABLE WHERE ID = @nxt_ID_Wanted)
 BEGIN

       RAISERROR('Selected ID value already exists',16,1)
       SET @nxt_ID_Wanted = 0;
       RETURN;
 END 
ELSE      -- else just let the user have the value he/she wanted
 BEGIN 
   SET @nxt_ID_Available = @nxt_ID_Wanted;
 END

END

Execute Procedure

DECLARE @ID INT;
EXECUTE dbo.usp_GetNextValue @nxt_ID_Wanted = 6
                             ,@nxt_ID_Available = @ID OUTPUT

SELECT @ID