auto increment stored procedure

Question

Hello,

 

I was able to get the following but not able to get the number to start from 900001.

 

DECLARE @new_seq INT=(

SELECT TOP 1

[NewNum] + 1

FROM [table] mo WITH(ROWLOCK,XLOCK,HOLDLOCK)

WHERE not([NewNum] is null) and NOT EXISTS

(

SELECT NULL

FROM [table] mi WITH(ROWLOCK,XLOCK,HOLDLOCK)

WHERE mi.[NewNum] = mo.[NewNum] + 1

)

ORDER BY

[NewNum]

);

IF @new_seq IS NULL

BEGIN SET @new_seq=900001;

END

ELSE

BEGIN SET @new_seq=@new_seq;

END

INSERT INTO dbo.[table]([NewNum]) VALUES(@new_seq);

 

Im trying to see which number is already there and whats missing, I would like for it to see between 900000 to 999999 but  this is what current data looks like:

 

NewNumber

904969

904984

904999

904998

905142

905141

 

I can put dummy record of 900000 then the above code will work but without putting that dummy info how can i get it to work?

 

Hope what i put up make sense.

Thanks