Auto Generated Serial Number using Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I want to create a procedure which would create a serial number using a stored procedure.

I have three tables:

VB

Table 1:

create table ItemTypes
(
    ItemTypeID int not null primary key,
    ItemType varchar(30)
)
Table 2:

create table ItemBatchNumber
(
     ItemBatchNumberID int not null primary key,
     ItemBatchNumber varchar(20),
     ItemType varchar(30),
)
Table 3:

create table ItemMfg
(
    ManufactureID int not null primary key,
    ItemBatchNumberID int foreign key references ItemBatchNumber(ItemBatchNumberID),
    MfgDate datetime 
)

For each Item Type there are several Item batch number.

Now, first 3 digit of serial no is xyz. 4 digit of serial no should be Item Type(e.g if Item type is 'b' then serial no should be xyzb).

5 digit of serial no should be like this:

In a day, for first Item batch number of a Item type, 5th digit should be 1 and it will remain 1 for that day.For the next different Item batch number it should be 2 and it will remain 2 for that day.
Same rule applied for next day and other item type.

e.g suppose 'b' Item Type has 3 Item batch number WB101,WB110,WB231. If today someone select WB110(Item batch number) of 'b' Item Type first then Serial No should be xyzb1 and it will remain xyzb1 for today for WB110. Now if someone select WR101 next then Serial No should be xyzb2 for today. Tomorrow which Item batch number of 'b' Item type will be selected first, for that batch number and that type serial no should be xyzb1. Same rule applied for other item type.
I have tried so far:
I have created a new table and stored data into that table and tried to check with columns of that new table.

Create table Gen_SN
(
ItemType varchar(10),
ItemBatchNumber varchar(10),
SerialNumber varchar(10),
mfgDate datetime
)

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

I am new to SQL. 4rth digit of SN is generated correctly from my code. Please give me some idea on generating 5th digit and let me know if you have any doubt. Thanks in advance.

Posted 28-Feb-15 17:04pm

Atanu Ghosh

Updated 2-Mar-15 16:20pm

v7

Add a Solution

Comments

John C Rayan 1-Mar-15 5:29am

what's the problem you are facing?

John C Rayan 1-Mar-15 5:41am

I can see a potential problem in your logic. Set @fifthDigit=SUBSTRING(@SerialNumber,5,1). Your ItemType type is varchar(30) so how can you get the substring from position (5,1) which could go wrong. Are you sure your itemtype always single character like 'b'.

Atanu Ghosh 1-Mar-15 10:20am

yes Item Type is single character like 'r',b,s. So its data type should be char right? I am facing problem on how to increment the value of fifth digit checking with dates when next different item batch number of a same item type or different item type is used. Please help.

John C Rayan 1-Mar-15 12:20pm

Where do you store your SN? you need some table to store the SN. SN should be persisted for ItemType , Date so that you could read from there and increment it.

Atanu Ghosh 1-Mar-15 12:24pm

yes I have to store it. But for now I just have to show the generated SN it. I am thinking of creating a column 'Serial Number' on ItemMfg table to store the generated SN.

John C Rayan 1-Mar-15 12:30pm

I could see you are counting the BatchNumber and then increment it. Where is your problem with incrementing the fifth digit. Can you post the error message

John C Rayan 1-Mar-15 12:34pm

I can see another problem. Also make sure that you define type char(1) for itemtype and let me know

if (@fifthDigit !=1)
set @fifthDigit=1
else set @fifthDigit=@fifthDigit+1

It should be

if (@fifthDigit=0)
set @fifthDigit=1
else set @fifthDigit=@fifthDigit+1

Atanu Ghosh 1-Mar-15 13:19pm

I am getting error while executing stored procedure: Conversion failed when converting the varchar value 'R' to data type int.

John C Rayan 1-Mar-15 15:17pm

You have to cast the count of unique itembatchnumbers to varchar in setting @serialnumber

John C Rayan 2-Mar-15 4:48am

What's the status? is it working?

Atanu Ghosh 2-Mar-15 21:28pm

I have edited ques. Please go through it. still I am not getting expecting result. Please help.

John C Rayan 3-Mar-15 5:54am

Hi I have posted a solution and let me know. You will get the idea what I have done.

2 solutions

Solution 1

Create a table which will be truncated and loaded daily through a batch.

SQL

Create table Gen_SN
(
ItemType varchar(10),
ItemBatchNumber varchar(10),
SerialNumber integer,
)

SQL

SELECT @ItemType=(SELECT ItemType FROM Gen_SN WHERE ItemBatchNumber=@ItemBatchNumber)
SELECT @SerialNumber1='xyz'+''+@ItemType

Next, check whether the itenBatchNumber already has any assigned serial number from table Gen_SN

SQL

IF @ItemType IS NOT NULL
BEGIN 
   SELECT @SN_5 = MAX(SerialNumber) FROM Gen_SN WHERE ItemType = @ItemType GROUP BY ItemType  --say, for item b, WB110 is visited first, WB101 is visited 2nd, and now WB231 is being visited, so here the variable value will be 2
    
   IF @SN_5 IS NULL --if no itemBatch is being visited under the item Type
        SELECT @SN_5 = 1 
   ELSE
        SELECT @SN_5 = @SN_5 + 1
   
   UPDATE Gen_SN
   SET  SerialNumber = @SN_5
   WHERE ItemBatchNumber=@ItemBatchNumber
END

SELECT @SerialNumber1 = @SerialNumber1 + CONVERT(@SN_5, VARCHAR(5))

Posted 2-Mar-15 20:59pm

RiyaB

Updated 2-Mar-15 21:00pm

v2

Add a Solution

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

John C Rayan · Accepted Answer · 2015-03-02T23:53:00

I have tested it and worked fine. Here I have removed the @ItemType from input parameter because we could get it from the other table. Otherwise you can include as before. Also I assume that you validate the mfgDate before passing that to this SP otherwise it will be wrong , as whatever the date is passed to the SP will create an entry into Gen_SN even if it is not a valid mfgDate. But you get the idea from this SP. You could also use CONCAT instead of + if you use SQL 2012 and above.

alter procedure Gen_SerialNumber
(
@ItemBatchNumber varchar(30),
@Date datetime,
@SerialNumber varchar(20) out
)
AS

BEGIN

DECLARE @ItemType char(1)

SELECT @ItemType = ItemType 
FROM ItemBatchNumber 
WHERE ItemBatchNumber=@ItemBatchNumber

IF NOT EXISTS (SELECT SerialNumber FROM Gen_SN  WHERE ItemType = @ItemType AND mfgDate = @Date AND ItemBatchNumber = @ItemBatchNumber)
	SET @SerialNumber = 'xyz'+@ItemType+'1' 
ELSE
	SELECT @SerialNumber = SUBSTRING(SerialNumber, 1,4) + CAST(CAST(SUBSTRING(SerialNumber,5,LEN(SerialNumber)-4) as INT) + 1 as VARCHAR) FROM Gen_SN  WHERE ItemType = @ItemType AND mfgDate = @Date AND ItemBatchNumber = @ItemBatchNumber

INSERT INTO Gen_SN(ItemType,ItemBatchNumber,SerialNumber,mfgDate) VALUES(@ItemType,@ItemBatchNumber,@SerialNumber, @Date) 

SELECT @SerialNumber

END

Auto Generated Serial Number using Stored Procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Auto Generated Serial Number using Stored Procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us