Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
2
Reply
auto increment stored procedure
Kev Patil
Jul 18 2017 4:13 PM
189
Reply
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
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
2
)
Next Recommended Forum
Sql Server Blocking Issue.
Find Only Non Null Value from Multiple Row