業務常需生成如 BH000001、BH000002 格式的單號。本文提供一個純 T-SQL 純量函數,自動查詢現有最大值並生成下一號。
CREATE FUNCTION dbo.f_NextBH()
RETURNS CHAR(8)
AS
BEGIN
DECLARE @next CHAR(8);
SELECT @next = 'BH' + RIGHT('000000' + CAST(ISNULL(CAST(RIGHT(MAX(BH), 6) AS INT), 0) + 1 AS VARCHAR(6)), 6)
FROM YourTable; -- 替換為實際存放流水號的表
RETURN @next;
END;
GO
MAX(BH):取現有最大單號 (如 BH000015)。RIGHT(..., 6):取後 6 位數字部份 (000015)。CAST(... AS INT):轉數值 15。ISNULL(..., 0):無資料時從 0 開始。+ 1:遞增 → 16。CAST(... AS VARCHAR(6)) → '16'。RIGHT('000000' + ..., 6):左補零 → '000016'。'BH' + ... → 'BH000016'。-- 插入新單據
DECLARE @newBH CHAR(8) = dbo.f_NextBH();
INSERT INTO Orders (BH, CustName, Amt) VALUES (@newBH, '客戶A', 1000);
SELECT @newBH; -- 顯示 BH000016
上述函數在高併發下會產生重複號碼 (SELECT MAX 與 INSERT 非原子)。生產環境必須採用以下任一機制:
CREATE SEQUENCE dbo.seq_BH AS INT START WITH 1 INCREMENT BY 1 CACHE 100;
-- 取號
SELECT 'BH' + RIGHT('000000' + CAST(NEXT VALUE FOR dbo.seq_BH AS VARCHAR(6)), 6);
CREATE TABLE Orders (
Id INT IDENTITY(1,1) PRIMARY KEY,
BH AS 'BH' + RIGHT('000000' + CAST(Id AS VARCHAR(6)), 6) PERSISTED,
CustName NVARCHAR(100),
Amt DECIMAL(12,2)
);
BH 索引 (PERSISTED)。CREATE TABLE Seq_BH (Id INT NOT NULL DEFAULT 0);
INSERT INTO Seq_BH VALUES (0);
-- 取號預存程序
CREATE PROC usp_GetNextBH @BH CHAR(8) OUTPUT
AS
BEGIN
BEGIN TRAN;
UPDATE Seq_BH SET Id = Id + 1;
SELECT @BH = 'BH' + RIGHT('000000' + CAST(Id AS VARCHAR(6)), 6) FROM Seq_BH;
COMMIT TRAN;
END
BH2024010001),SEQUENCE 需搭配年度重置 Job,或改用 MAX(CASE WHEN BH LIKE 'BH202401%' THEN ...) 邏輯。