[MSSQL] 使用 SQL 產生流水單號

近期開發專案需要使用 SQL 產生西元日期搭配流水號作為單號，例如 P221027001，若當天已經存在第一筆單號，則找到當日最大的單號，將流水號遞增產生下一筆單號，例如 P221027002。

需求很簡單，但考慮到開發的專案是 Web，如果將當日最大單號取出再由後端程式碼產生新單號，當 User 確定存檔後再將單號與資料送至資料庫執行，過程中一來一往，假設當兩個 User 幾乎同時操作時，有極小的機率拿到相同的單號傳回資料庫作業。礙於可能產生重複單號，最後決定當資料送回資料庫時，執行預存再產生新單號，縮小產生新單號與儲存資料之間的時間。

原先預期是由預存程序來執行儲存資料以及產生單號，但在撰寫預存時覺得這樣不太好，該預存命名的定義為儲存資料，裡面竟然偷偷再做產生單號的動作，於是決定將產生單號的動作改為由純量函數執行，下方是純量函數預設新增的 Script：

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

在 BEGIN 和 END 之間加入產生單號的邏輯。

DECLARE @RESULT VARCHAR(10) --最終返回的值
DECLARE @MAX AS VARCHAR(10) --從資料表中找到的最大單號
DECLARE @HEADER VARCHAR(8) --扣除流水號的單號頭部

-- 取出當天日期作為單號的一部分: 20221027
SET @HEADER = CONVERT(VARCHAR, GETDATE(), 112)

-- 去除日期前兩碼: 221027
SET @HEADER = SUBSTRING(@HEADER, 3, LEN(@HEADER) - 2)

-- 組裝單號的頭部: P221027
SET @HEADER = 'P' + @HEADER

-- 從資料表欄位取回當天最大單號
SET @MAX = (SELECT MAX(ORDERNO) FROM DBO.ORDERS WHERE ORDERNO LIKE @HEADER + '%')
-- 測試假設存在單號
-- SET @MAX = 'P221027002'
IF @MAX IS NULL -- 如果沒找到 @MAX 會是 NULL
    BEGIN
        -- 當天還沒有單號, 直接將 @HEADER 和流水號組裝: P221027001
        SET @RESULT = @HEADER + '001'
    END
ELSE -- 資料表內已經有當天單號
    BEGIN
        DECLARE @VAL VARCHAR(3)

        -- 取最大單號後三位流水號
        SET @VAL = SUBSTRING(@MAX, LEN(@HEADER) + 1, 3)

        -- 轉成 INT 後加一, 再轉回 VARCHAR
        SET @VAL = CONVERT(VARCHAR, CONVERT(INT, @VAL) + 1)

        -- 使用 REPLICATE 函數將流水號前面補零至為三位
        SET @VAL = (REPLICATE('0', 3 - LEN(@VAL)) + @VAL)

        -- 組裝 @HEADER 和流水號: P221027005
        SET @RESULT = @HEADER + @VAL
    END

-- 返回值
RETURN @RESULT

完成後，將函數命名為 dbo.fnGetNewOrderNo ，執行函數新增，呼叫函數用 SELECT <SCHEMA.FUNCTIONNAME>() 。

INSERT INTO [DBO].[ORDERS]
(
    [ORDERNO]
)
OUTPUT inserted.ORDERNO
VALUES
(
    (SELECT dbo.fnGetNewOrderNo()) --呼叫產生單號函數
)

利用 OUTPUT inserted.<column> 查看指定欄位的執行結果。

如果單號需要西元四碼，例如 20221027，將下面這行移除以及調整 VARCHAR 大小。

-- 去除日期前兩碼: 221027
SET @HEADER = SUBSTRING(@HEADER, 3, LEN(@HEADER) - 2)

打完，收工！

---

Thanks for reading the article 🌷 🌻 🌼

If you like it, please don't hesitate to click heart button ❤️
or follow my GitHub ⭐ I'd appreciate it.

---