使用存储过程或函数自动生成的数字 [英] Auto generated number using stored procedure or function

问题描述

我有一张随机数字表。格式是前两个始终为90（数字开头），后四个可以是任意组合，如此。我想添加的其他东西是程序会检查号码是否已经存在，如果它确实跳过了那个号码等等。



编辑：id喜欢提到的是这个存储过程，我试图从900001开始到结束。当这样做时，当表格中已经存在当前数字时，那就是当它跳到下一个时。



我尝试了什么：



我试过这样：

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..

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.

推荐答案

SQL有 RAND [ ^ ]创建随机数的功能...



- - 更新OP的评论更新

你有3个选择。

1.随机，这意味着非独特（即兰德）

2独特，但是非随机（顺序） - 标识栏 [ ^ ]

3.两者 - GUID [ ^ ]

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[^]

这篇关于使用存储过程或函数自动生成的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！