Auto generated number using stored procedure or function

Question

0.00/5 (No votes)

See more:

I have a table with random numbers. Format is first two always 90 (number starts with) and last four can be any combination, like this. And other thing i'd like to add is procedure would check if number already exists or not, if it does skip that number, etc..

EDIT: id like to mention is with this stored procedure, im trying to start from 900001 to end. While doing this itll come to when current number already exists in table, thats when itll skip to next.

What I have tried:

I have tried following this:

Alter procedure Gen_SerialNumber
(
@ItemType char(1),
@ItemBatchNumber varchar(30),
@Date datetime,
@SerialNumber varchar(20) out,
@fifthDigit int
)
AS
Begin 
set @ItemType=(Select ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber)
Declare @SerialNumber1 varchar(20)
Set @SerialNumber1='xyz'+''+@ItemType+''+CAST( (Select COUNT(distinct ItemBatchNumber)from ItemBatchNumber
where ItemType=@ItemType) as varchar (10) )
Set @fifthDigit=SUBSTRING(@SerialNumber1,5,1)
if exists(Select SerialNumber from Gen_SN where SerialNumber=null or SerialNumber!=@SerialNumber)
set @fifthDigit=1
if exists(Select mfgDate,ItemBatchNumber from Gen_SN where mfgDate=@Date and ItemBatchNumber=@ItemBatchNumber)
Set @fifthDigit=1
else 
set @fifthDigit=@fifthDigit+1
 
Set @SerialNumber=('xyz'+''+@ItemType+''+cast(@fifthdigit as varchar(2)))
Insert into Gen_SN values(@ItemType,@ItemBatchNumber,@SerialNumber,@Date)
END

Got that from here but unable to figure out since my situation is little different.

Posted 18-Jul-17 4:00am

r00n3y

Updated 18-Jul-17 4:43am

v2

Add a Solution

Comments

ZurdoDev 18-Jul-17 10:41am

What is your question?

r00n3y 18-Jul-17 11:06am

how can i get next sequence number when table doesnt have all in sequence, and when it gets it (make sure its unique).?? See screenshot i posted

ZurdoDev 18-Jul-17 11:11am

There is no magic way to do it. You just have to write code. Write a function that has a loop and you keep looping until you find the next one that is not used.

r00n3y 18-Jul-17 11:20am

I know there is no magic way, and writing is where my knowledge lacks.

ZurdoDev 18-Jul-17 11:27am

Look up how to do a loop in sql and start with that.

r00n3y 18-Jul-17 15:50pm

I got the following:
DECLARE @new_seq INT=(
SELECT TOP 1
[NewLeaseNumber] + 1
FROM [LeaseNumber] mo WITH(ROWLOCK,XLOCK,HOLDLOCK)
WHERE not([NewLeaseNumber] is null) and NOT EXISTS
(
SELECT NULL
FROM [LeaseNumber] mi WITH(ROWLOCK,XLOCK,HOLDLOCK)
WHERE mi.[NewLeaseNumber] = mo.[NewLeaseNumber] + 1
)
ORDER BY
[NewLeaseNumber]
);
IF @new_seq IS NULL
BEGIN SET @new_seq=900001;
END
ELSE
BEGIN SET @new_seq=@new_seq;
END
INSERT INTO dbo.[LeaseNumber]([NewLeaseNumber]) VALUES(@new_seq);

But problem is my table had only few hundreds number and there is no 900001, so i would like it to start from there. Any tips?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2017-07-18T04:13:00

Solution 1

SQL has a RAND[^] function to create random numbers...

-- UPDATE TO REFLECT OP'S COMMENT
You have 3 options.
1. Random, and that means non-unique (that's RAND)
2. Unique, but not random (sequential) - identity column[^]
3. Both - GUID[^]

Posted 18-Jul-17 4:13am

Kornfeld Eliyahu Peter

Updated 18-Jul-17 8:22am

v2

Comments

r00n3y 18-Jul-17 10:44am

i have looked at it but problem is i cant figure out a way to check if number exists already or not in table

Aria Jafarian 18-Jul-17 10:52am

Just thinking out loud, Shouldn't you put each generated number in a table and check if it exists or not in that table before using it?

Dave Kreskowiak 18-Jul-17 10:52am

How about a SELECT for the ID number you generated? Seems kind of obvious.