使用存储过程自动生成的序列号

Question

我想创建一个过程，该过程将使用存储过程创建序列号。

我有三张桌子：

表1:

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

表2:

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

表3:

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

对于每种物料类型，都有多个物料批号。

现在，序列号的前3位是xyz。序列号的4位数字应为项目类型（例如，如果项目类型为"b"，则序列号应为xyzb）。

5位序列号应该是这样的：

在一天中，对于项目类型的第一个项目批次号，第5位数字应为1，并且当天将保持为1。对于下一个Item批号，它应该是2，并且当天将保持2。

第二天，同样的规则适用。

例如，假设"b"项目类型具有3个项目批号WB1、WB2、WB3。如果今天有人首先选择了"b"项目类型的WB2（项目批号），则序列号应为xyzb1，并且今天WB2的序列号将保持xyzb1。现在，如果有人下一次选择WR1，那么今天的序列号应该是xyzb2。明天将首先选择"b"项目类型的项目批号，该批号和该类型的序列号应为xyzb1。相同的规则适用于其他项目类型。

我已经试过了：

   create procedure Gen_SerialNumber
    (
    @ManufactureID int,
    @IitemType varchar(30),
    @ItemBatchNumberID int,
    @Date datetime,
     @SerialNumber out,
    @fifthDigit int out
    )
    AS
    Begin 
    set @IitemType=(Select ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber)
    Declare @SerialNumber1 varchar(20)
    Set @SerialNumber1= 'xyz'+''+@IitemType+''+CAST( (Select COUNT(distinct ItemBatchNumber)from ItemBatchNumber
    where   ItemType=@IitemType) as varchar (10) )
    Set @fifthDigit=SUBSTRING(@SerialNumber1,5,1)
   IF EXISTS(SELECT SerialNumber FORM ItemMfg WHERE SerialNumber=null or         SerialNumber!=@SerialNumber)
                          SET @fifthDigit=1
        IF EXISTS(SELECT mfgDate,ItemBatchNumberID FROM ItemMfg WHERE mfgDate=@Date and ItemBatchNumberID=@ItemBatchNumberID)
                          SET @fifthDigit=1
                     ELSE 
                          SET @fifthDigit=@fifthDigit+1
    SET @SerialNumber=('xyz'+''+@ItemType+''+cast(@fifthdigit as varchar(2)))
INSERT INTO ItemMfg VALUES(@ItemType,@ItemBatchNumberID,@SerialNumber,@Date)
END

我是SQL的新手。SN的第4位数字是根据我的代码正确生成的。我面临的问题是，当使用同一物品类型或不同物品类型的下一个不同物品批号时，如何用日期增加第五位检查的值。如果你有任何疑问，请告诉我。提前谢谢。

Answer 1

您的问题有几个问题：我对存储过程中WireBatchNumber列的出现感到困惑，因为您提供的表定义中不包括该列。其次，最后一个@ItemType（就在end命令之前）拼写错误。

我认为这里的挑战是，您需要存储过程在批次中增加一个变量，并每天"重新开始"。这对我来说意味着你需要这个程序1.追踪上次跑步的时间，看看是不是今天。2.跟踪它今天运行了多少次。

这不是一个非常初级的知识类型的任务，但显然有一种方法：如何在SQL Server中声明全局变量。。？。使用此链接中提到的变量类型，您可以使用DateDiff（）函数设置一些条件结构，将日期变量与今天进行比较，更改日期变量并重置计数器（如果这两个日期在不同的日期），然后递增项目批号的计数器，并使用此计数器提供第五位数字。

Answer 2

让我们填充一些数据：

项目类型：

ItemTypeID  ItemType
1           a
2           b
3           c

项目批次号：

ItemBatchNumberID   ItemBatchNumber ItemType
...
11                  WB1             b
22                  WB2             b   !
33                  WB3             b   !!
44                  WB1             c
55                  WB2             c   !
66                  WB3             c   !!
77                  WB3             c   !!
...

项目制造：

ManufactureID   ItemBatchNumberID   MfgDate
111             22                  2015-03-01 7:00 -> xyzb1
222             11                  2015-03-01 8:00 -> xyzb1
333             22                  2015-03-01 9:00 -> xyzb2
444             33                  2015-03-02 5:00 -> xyzb1
555             33                  2015-03-02 6:00 -> xyzb2
666             11                  2015-03-02 7:00 -> xyzb1
777             33                  2015-03-02 8:00 -> xyzb3
888             11                  2015-03-02 9:00 -> xyzb2
999             22                  2015-03-02 9:35 -> xyzb1

我看到了一些不恰当的东西——这不是必要的，意味着有错误。对不起，我不知道真正的商业规则——它们可能会对事情有不同的解释。我假设最简单和最有用的逻辑。

1. ItemTypes-看起来像查找表。但是您不使用ItemTypeID，而是使用它的值（a，b，c）作为唯一代码

所以表格应该是这样的：

ItemType(PK)    ItemTypeDescr
a               Type A
b               Most Usable Type B
c               Free text for type C

2. ItemBatchNumber-匹配表-定义批次和ItemTypes之间的匹配。它可能有用"！"标记的数据，这是有效的

为了避免77，需要在ItemBatchNumber+ItemType上添加一些唯一索引或设置PK。但在任何情况下，设定为22、55和11、44都是正常的。因此，您的SP将出现故障。

3. 在这里你会得到一个错误

这里的查询可以返回多个行（22，55）：

set @IitemType=(Select ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber)

或者：

Select @IitemType = ItemType from ItemBatchNumber where ItemBatchNumber=@ItemBatchNumber

如果是多行，它将返回最后一行。

但无论如何，这都是不正确的-如果@ItemBatchNumber="WB2"，则需要哪个值为"b"或"c"？

4. 错误：

... and ItemTypeID=@IitemType ...

ItemTypeID为int；@littemType是char（'b'）-你期望什么？

MfgDate=@Date

日期"2015-03-02 5:00"one_answers"2015-03-027:00"不相同，但在同一天。

...from ItemBatchNumber,ItemMfg
where MfgDate=@Date 
    and ItemBatchNumber=@ItemBatchNumber 
    and ItemTypeID=@IitemType

即使ItemBatchNumber将返回一行，并且日期不计算时间，它也将从一天起返回ItemMfg的所有批次。你需要做适当的加入。

Select COUNT(distinct ItemBatchNumber)

你总是需要（count（）+1），而不是一个不同的

5. 我不确定，当你需要生成序列号时：

a。在添加新ItemMfg之前，需要检查当前日期。

b。对于任何已存在的ItemMfg行（对于555），则需要从count（）中排除555之后的所有rqws。

---

这就是查询的样子：

a。（添加新的ItemMfg行时-传递用于创建ItemMfg列的值）

create procedure AddNewItemMfg    -- ...and generate SN
(
   @ItemBatchNumberID int,
   @MfgDate datetime        -- pass getdate()
) AS Begin 
-- calc 5th digit:
declare @fifthDigit int;
select 
    @fifthDigit = count(*) + 1
from ItemBatchNumber AS bb
inner join ItemMfg ii ON ii.ItemBatchNumberID = bb.ItemBatchNumberID 
where bb.ItemBatchNumberID = @ItemBatchNumberID             -- single ItemBatchNumber-row to get ItemType
    and ii.MfgDate <= @MfgDate                              -- all previous datetimes
    and cast(ii.MfgDate as date) = cast(@MfgDate as date)   -- same day
-- ManufactureID is Identity (i.e. autoincremented)
INSERT INTO ItemMfg (ItemBatchNumberID, MfgDate, SerialNumber)
    SELECT @ItemBatchNumberID
        , @MfgDate
        , 'xyz' + bb.ItemType + cast(@fifthDigit as varchar(5))
    FROM ItemBatchNumber bb
    WHERE bb.ItemBatchNumber = @ItemBatchNumber
;
end

b。对于任何已存在的ItemMfg行

create procedure GenerateSNforExistedItemMfg
(
   @ManufactureID int
) AS Begin 
-- generate SN - same as previous but in single query
declare @SN varchar(10);
Select @SN = 'xyz'
    + bb.ItemType
    + cast(
        select count(*) + 1
        from ItemMfg mm                         -- single row from mm
        inner join ItemBatchNumber AS bb        -- single row from bb
            ON bb.ItemBatchNumberID = mm.ItemBatchNumberID
        inner join ItemMfg ii                   -- many matched rows
            ON ii.ItemBatchNumberID = bb.ItemBatchNumberID 
        where mm.ManufactureID = @ManufactureID -- single row from mm
            and ii.MfgDate <= mm.MfgDate        -- all previous datetimes
            and cast(ii.MfgDate as date) = cast(mm.MfgDate as date) -- same day
    as varchar(5))
FROM ItemBatchNumber AS bb
INNER JOIN ItemMfg ii ON ii.ItemBatchNumberID = bb.ItemBatchNumberID 
WHERE ii.ManufactureID = @ManufactureID 
-- update SN
UPDATE ItemMfg SET
    SerialNumber = @SN
WHERE ii.ManufactureID = @ManufactureID;
end