连续发票编号 SQL 服务器

Question

我有一个发票表，其中包含发票ID（整数标识）主键。 和一列发票编号，它是一个中间人。我有另一个表用于生成名为 Invoice_Numbers 的发票编号（见下文）。为了确保发票编号是唯一的并防止出现空白，我实施了下面的代码。有人可以查看此代码并评论其可靠性吗？在多用户环境中运行 SQL 2008。

用户在插入发票时致电时获得相同发票号码的几率有多大？

如果存在   （选择 *    从系统对象    其中object_id = OBJECT_ID（N'[进口].[Invoices_Numbers]'）           并输入 （ N'U' ））  删除表 [导入]。[Invoices_Numbers]
去
创建表 [导入]。[Invoices_Numbers]  (     [发票编号][国际]标识（1， 1） 不为空     ，[删除] [位] 不为空     ，[使用] [位] 不为空，     约束 [PK_Invoices_Numbers] 主键群集 （ [发票编号] ASC ）与 （PAD_INDEX = 关闭， STATISTICS_NORECOMPUTE = 关闭， IGNORE_DUP_KEY = 关闭， ALLOW_ROW_LOCKS = 打开， ALLOW_PAGE_LOCKS = 打开） 在 [主] 上  )在 [主要]
去
更改过程 [导入]。[Get_Invoice_Number](  @InvoiceNumber输出)如  开始      声明@NewNumber INT      声明@MinNumber INT
   BEGIN TRAN
  SELECT @MinNumber = MIN(InvoiceNumber)
  FROM   Imports.Invoices_Numbers
  IF @MinNumber > 1
    BEGIN
        SET IDENTITY_INSERT Imports.Invoices_Numbers ON;
        INSERT Imports.Invoices_Numbers
        (
          Invoicenumber
          ,Deleted
          ,Used
        )
        VALUES
        (
          1
          ,0
          ,1
        )
        SET IDENTITY_INSERT Imports.Invoices_Numbers OFF;
        SET @NewNumber=1
    END
  ELSE
    BEGIN
        WITH Gaps
             AS (SELECT TOP 1 a.InvoiceNumber + 1 AS GapValue
                 FROM   Imports.Invoices_Numbers a
                 WHERE  NOT EXISTS
                            (SELECT *
                             FROM   Imports.Invoices_Numbers b
                             WHERE  b.InvoiceNumber = a.InvoiceNumber + 1)
                        AND a.InvoiceNumber <
                            (SELECT MAX(InvoiceNumber)
                             FROM   Imports.Invoices_Numbers))
        SELECT @NewNumber = GapValue
        FROM   Gaps
        IF @NewNumber IS NULL
          BEGIN
              SELECT TOP 1 @NewNumber = InvoiceNumber
              FROM   Imports.Invoices_Numbers
              WHERE  Used = 0
                     AND Deleted = 0
              ORDER  BY InvoiceNumber
              IF @NewNumber IS NULL
                BEGIN
                    INSERT Imports.Invoices_Numbers
                    (
                      Deleted
                      ,Used
                    )
                    VALUES
                    (
                      0
                      ,1
                    )
                    SELECT @NewNumber = SCOPE_IDENTITY ()
                END
              ELSE
                BEGIN
                    UPDATE Imports.Invoices_Numbers
                    SET    Used = 1
                    WHERE  InvoiceNumber = @NewNumber
                END
          END
        ELSE
          BEGIN
              SET IDENTITY_INSERT Imports.Invoices_Numbers ON;
              INSERT Imports.Invoices_Numbers
              (
                Invoicenumber
                ,Deleted
                ,Used
              )
              VALUES
              (
                @NewNumber
                ,0
                ,1
              )
              SET IDENTITY_INSERT Imports.Invoices_Numbers OFF;
          END
    END
  SELECT @InvoiceNumber = @NewNumber
  COMMIT TRAN
 

结束

Answer 1

您的解决方案看起来有点复杂。 我不会对数据导入任务以外的任何内容使用 set identity_insert 进行评论。

为了确保唯一性，我首先添加一个唯一约束：

alter table Invoices add constraint UX_Invoices_InvoiceNr unique

然后，您可以使用如下 SQL 语句插入发票：

while 1=1
    begin        
    declare @new_nr int
    select  @new_nr = max(InvoiceNr) + 1
    from    dbo.Invoices
    if @new_nr is null
        set @new_nr = 1
    insert  dbo.Invoices
            (InvoiceNr, ...)
    values  (@new_nr, ...)
    if @@rowcount = 1
        break
    end