[MSSQL] 群組流水號(日期流水號)

簡介

專案上常遇到(訂單)編號的流水號需要根據日期來作編號(ex:201901090001)，以下就來簡單紀錄如何實作。

SQL

建立資料表(Table)與預存程式(Stored Procedure)

CREATE TABLE [dbo].[FlowTable](
 [FlowGroup] [varchar](30) NOT NULL,
 [FlowSubGroup] [varchar](30) NOT NULL,
 [FlowNumber] [int] NULL,
 CONSTRAINT [PK_FlowTable] PRIMARY KEY CLUSTERED 
(
 [FlowGroup] ASC,
 [FlowSubGroup] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[uspGetFlowNumber]
    @groupName      VARCHAR(30),
    @subgroupName   VARCHAR(30),
    @flowNumber     INT = 1 OUTPUT
AS
BEGIN

    IF EXISTS( SELECT TOP 1 1 FROM dbo.FlowTable WHERE FlowGroup = @groupName AND FlowSubGroup = @subgroupName )
    BEGIN
        UPDATE dbo.FlowTable
        SET @flowNumber = FlowNumber = FlowNumber + 1
        WHERE FlowGroup = @groupName AND FlowSubGroup = @subgroupName
    END
    ELSE
    BEGIN
        INSERT INTO dbo.FlowTable( FlowGroup, FlowSubGroup, FlowNumber )
        VALUES( @groupName, @subgroupName, 1 )
        SET @flowNumber = 1
    END
    RETURN @flowNumber

END

Table 欄位說明

• FlowTable: 流水號資料表
  • Primary Key: PK_FlowTable

Column Name	Name	Data Type	IsNULL
FlowGroup	流水號群組	varchar(20)	N
FlowSubGroup	流水號子群組	varchar(20)	N
FlowNumber	流水號	int	Y

執行方式

呼叫dbo.uspGetFlowNumber取得設定之流水編號(@flowNumber)

DECLARE @flowNumber INT
EXEC dbo.uspGetFlowNumber 'MsgID', '20140831', @flowNumber OUT
SELECT @flowNumber

執行結果

得到目前流水編號

執行後FlowTable 資料表資料

應用

也可以針對日期做該日期的流水號

DECLARE @flowNumber INT
EXEC dbo.uspGetFlowNumber '20141003', '', @flowNumber OUT
SELECT @flowNumber

如有錯誤或建議，歡迎留言指教，謝謝!!
(相關內容如有侵犯隱私或著作權，請協助通知刪除，感謝)