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.