Auto Generated Serial Number using Stored Procedure

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

I have three tables:

 **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 till now:

 create procedure Gen_SerialNumber
 @IitemType varchar(30),
 @ItemBatchNumber varchar(30),
 @Date datetime,
 @fifthDigit int
 set @IitemType=(Select ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber)
 Declare @SerialNumber varchar(20)
 Set @SerialNumber= 'xyz'+''+@IitemType+''+CAST( (Select COUNT(distinct ItemBatchNumber)from ItemBatchNumber,ItemMfg
 where MfgDate=@Date and ItemBatchNumber=@ItemBatchNumber and ItemTypeID=@IitemType) as varchar (10) )
 Set @fifthDigit=SUBSTRING(@SerialNumber,5,1)
 if (@fifthDigit !=1)
 set @fifthDigit=1
 else set @fifthDigit=@fifthDigit+1
 Select 'SN: xyz'+''+@IitemType+''+@fifthDigit

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.

more ▼

asked Mar 01, 2015 at 04:51 PM in Default

avatar image

1 1 1 2

The 5th item is bound by the definition of a day - which day? The day it is requested?

Mar 02, 2015 at 08:20 AM Kev Riley ♦♦

with mfgDate,the date in which serial number generated.

Mar 03, 2015 at 04:29 AM ats
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 01, 2015 at 04:51 PM

Seen: 1286 times

Last Updated: Mar 03, 2015 at 04:29 AM

Copyright 2018 Redgate Software. Privacy Policy