Kev Patil

Kev Patil

  • NA
  • 21
  • 3.9k

auto increment stored procedure

Jul 18 2017 4:13 PM
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 

Answers (2)