SQL Server 流水單號FUNCTION練習

在網路上買東西都有訂單流水編號

很可惜，不能用自動編號IDENTITY

因為有跳號及上限的風險

所以最好自已寫個FUNCTION比較適合

希望的結果是：

10305240001
10305240002
10305240003
....

抓出年月日後，並自動依時間順序賦予單號
每天都可以有9999筆，若不夠再補上位數就好

先Create一張練習的訂單表：

USE 練練
GO
CREATE TABLE 練習自動訂單號
(
 訂單編號 VARCHAR(11) DEFAULT dbo.練習訂單編號(),
 訂單總價 MONEY,
 訂單時間 DATETIME DEFAULT GETDATE()
)
GO

說明：
DEFAULT dbo.練習訂單編號()
這個表若單獨執行，一定錯，因為dbo.練習訂單編號()
還沒建，要先執行下面的FUNCTION，上面的表才會成立


主FUNCTION：

CREATE FUNCTION dbo.練習訂單編號() RETURNS VARCHAR(11) 
AS
BEGIN
 DECLARE @firstOrderId VARCHAR(11) 
 DECLARE @lastOrderId VARCHAR(11)  --表內目前最後的單號
 DECLARE @finalOrderId VARCHAR(11) --最後新增回傳的單號
 DECLARE @yy VARCHAR(3) --年(民國)
 DECLARE @mm VARCHAR(2) --月
 DECLARE @dd VARCHAR(2) --日
 DECLARE @numS VARCHAR(4) --取出/放入的流水號
 DECLARE @num INT
 -------------------------------------------------------------
 SET @yy = CONVERT(VARCHAR,DATEPART(YEAR,GETDATE())-1911)
 SET @mm = CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))
 SET @dd = CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))
  SET @yy = CONVERT(VARCHAR,DATEPART(YEAR,GETDATE())-1911)
 SET @mm = CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))
 SET @dd = CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))
 IF LEN(@mm) < 2 SET @mm= '0'+@mm  --抓出月的位數，只有個位數自動補0
 IF LEN(@dd) < 2 SET @dd= '0'+@dd  --抓出日的位數，只有個位數自動補0
 SET @firstOrderId = @yy+@mm+@dd --將年月日結合
 
 -------------------------------------------------------------
 --找出最後一筆，並將該值賦予給@lastOrderId------------------------
 SELECT TOP(1) @lastOrderId=訂單編號 
 FROM 練習自動訂單號
 WHERE 訂單編號 LIKE(@firstOrderId+'%')
 ORDER BY 訂單編號 DESC
 
 -------------------------------------------------------------
 --若筆數為0，表示第1筆------------------------------------------
 --若有筆數，找出最後1位數+1並賦予回去@finalOrderId ---------------
 IF(@@ROWCOUNT=0)
  SET @finalOrderId=@firstOrderId+'0001'
 ELSE
  BEGIN
   SET @numS = SUBSTRING(@lastOrderId,8,4)
   SET @num = CAST(@numS AS INT) + 1
   SET @numS =REPLICATE('0',4-LEN(@num)) + CAST(@num AS VARCHAR)
             --抓出位數，自動補零為4位數
   SET @finalOrderId = @firstOrderId + @numS
  END
 -------------------------------------------------------------
 RETURN @finalorderId --傳回值
END
GO