[MSSQL] 使用Stored Procedure 產生以日期為群組的流水號ID

2021-07-13

ex: S210101001，S為前綴字，210101為日期(yyMMdd)，001為流水號

因為工作上的需求，

需要產生「S210101001」像這種以日期為群組的流水序號當成Table的PK，

一般最簡單的做法是「從目標Table取最大值再進行+1」，

但為了避免每次都要從目標Table內查最大值，

若資料量大可能會有效能問題，

於是選擇用一張Table進行Key的控管

Table Schema

欄位名稱	說明	Nullable	PrimaryKey
KeyType	索引鍵類型		Y
KeyValue	最新索引鍵編號	Y

/****** Object:  Table [dbo].[KeyData]    Script Date: 2021/7/13 下午 06:23:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[KeyData](
	[KeyType] [nvarchar](20) NOT NULL,
	[KeyValue] [nvarchar](50) NULL,
 CONSTRAINT [PK_KeyData] PRIMARY KEY CLUSTERED 
(
	[KeyType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引鍵類型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'KeyData', @level2type=N'COLUMN',@level2name=N'KeyType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新索引鍵編號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'KeyData', @level2type=N'COLUMN',@level2name=N'KeyValue'
GO

另外為了避免同時取號會取到重覆值的情況發生，

故採用Stored Procedure的方式進行取號，

並且針對取值與賦值的部分加入Transaction

Stored Procedure Schema


/****** Object:  StoredProcedure [dbo].[usp_GetNewID]    Script Date: 2021/7/13 下午 05:59:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Charley.Chang
-- Create date: 2021/07/12
-- Description:	Get New ID S+yyMMdd+001 (ex: S210712001)
-- =============================================
ALTER  PROCEDURE [dbo].[usp_GetNewID]
AS
BEGIN
	-- Declare Variables
	DECLARE @KeyType nvarchar(10)
	DECLARE @Prefix nvarchar(1)
	DECLARE @NewID nvarchar(20)
	DECLARE @DT nvarchar(20)
	DECLARE @NewSN nvarchar(4)
	DECLARE @ErrCnt int,@RowCnt int	-- Declare Variables to catch row modify counts 

	-- Set Variable
	SET @KeyType = 'SalesOrder'
	SET @Prefix = 'S'

	BEGIN TRANSACTION
	-- Set Datetime String yyMMdd (ex: 210712)
	SET @DT = SUBSTRING(CONVERT(varchar(10), GetDate(), 112), 3, 6)

	-- Set SN String 00X (ex: 001)
	SET @NewSN = (SELECT TOP 1 FORMAT(CAST(ISNULL(MAX(substring(kd.KeyValue, 8, 3)), '') AS INT) + 1, '00#') AS sno
    FROM dbo.KeyData kd WITH(HOLDLOCK, UPDLOCK)
	WHERE kd.KeyType = @KeyType AND kd.KeyValue LIKE CONCAT(@Prefix, @DT, '%'))

	IF (LEN(@NewSN) = 3)	-- To avoid the sn out of range
		BEGIN
			-- Concat ID S+yyMMdd+001 (ex: S210712001)
			SET @NewID = Concat(@Prefix, @DT, @NewSN)

			UPDATE dbo.KeyData SET dbo.KeyData.KeyValue = @NewID WHERE dbo.KeyData.KeyType = @KeyType
			SELECT @ErrCnt = @@ERROR	-- Update Error Count
			SELECT @RowCnt = @@ROWCOUNT	-- Update Count

			-- Check Success
			IF (@ErrCnt = 0 AND @RowCnt = 1)
				BEGIN	-- transaction success
					COMMIT TRANSACTION
				END
			ELSE	-- transaction failed, set new id is empty
				BEGIN
					ROLLBACK TRANSACTION;
					SET @NewID = ''
				END
		END
	ELSE	-- sn out of range, set new id is empty
		BEGIN
			ROLLBACK TRANSACTION;
			SET @NewID = ''
		END
	SELECT @NewID AS NewID
END

各變數說明：

變數名稱	用途說明
`@KeyType`	索引鍵類型，對應Table的KeyType欄位
`@Prefix`	前綴字
`@NewID`	回傳最新ID
`@DT`	日期字串(上方範例為yyMMdd)
`@NewSN`	該群組最新流水號(上方範例為00#)
`@ErrCnt`	用於取得Update時錯誤筆數`@@ERROR`
`@RowCnt`	用於取得Update更新完成筆數`@@ROWCOUNT`