สร้าง Auto ID หรือลำดับที่มีตัวอักษร ปี, เดือน บน SQL Server ด้วย Stored Procedure

CREATE TABLE [dbo].[GenerateNumber](
	[Sequence] [int] NOT NULL,
 CONSTRAINT [PK_GenerateNumber] PRIMARY KEY CLUSTERED 
(
	[Sequence] ASC
)) ON [PRIMARY]

GO

INSERT INTO GenerateNumber (Sequence) VALUES ('1');

CREATE PROCEDURE [dbo].[GetAutoIDNo]
	@sPrefix				VARCHAR(10),
	@iLength				INT,

	@sAutoID				VARCHAR(14) OUTPUT
AS
SET DATEFORMAT DMY;  
BEGIN

	SET NOCOUNT ON;

	DECLARE	@iSeq		INT = 0;
	
	BEGIN TRY
		SELECT @iSeq = ISNULL(Sequence,0) FROM GenerateNumber WITH (NOLOCK) ;

		SET @sAutoID	= @sPrefix + '-' + CAST(REPLACE(STR(@iSeq, @iLength), SPACE(1), '0')  AS VARCHAR(20))
		SET @iSeq = @iSeq + 1;
		UPDATE GenerateNumber SET  [Sequence] = @iSeq;
		
	END TRY
	BEGIN CATCH
		SET @sAutoID		=	@sPrefix + '-00000';
	END CATCH

	RETURN 0;
END

DECLARE	@return_value int,
		@sAutoID varchar(14)

EXEC	@return_value = [dbo].[GetAutoIDNo]
		@sPrefix = N'TC',
		@iLength = 5,
		@sAutoID = @sAutoID OUTPUT

SELECT	@sAutoID as N'@sAutoID'

CREATE TABLE [dbo].[GenerateNumber](
	[Year] [int] NOT NULL,
	[Month] [int] NOT NULL,
	[Sequence] [int] NOT NULL,
 CONSTRAINT [PK_GenerateNumber] PRIMARY KEY CLUSTERED 
(
	[Year] ASC,
	[Month] ASC,
	[Sequence] ASC
)) ON [PRIMARY]

CREATE PROCEDURE [dbo].[GetAutoIDNo]
	@sPrefix				VARCHAR(10),
	@iLength				INT,

	@sAutoID				VARCHAR(30) OUTPUT
AS
SET DATEFORMAT DMY;  
BEGIN

	SET NOCOUNT ON;
	DECLARE	@iYear		INT = YEAR(GETDATE());
	DECLARE	@iMonth		INT = MONTH(GETDATE());
	DECLARE	@iSeq		INT = 0;
	
	BEGIN TRY
		SELECT @iSeq = ISNULL([Sequence],0) FROM GenerateNumber WITH (NOLOCK) WHERE [Year] = @iYear AND [Month] = @iMonth;
		IF @iSeq = 0
			BEGIN
				SET @iSeq = 1;
				INSERT INTO GenerateNumber ([Year],[Month],[Sequence]) VALUES (@iYear, @iMonth, @iSeq);
			END

		SET @sAutoID	= @sPrefix + '-' 
				+ CAST(REPLACE(STR(@iYear, 4), SPACE(1), '0')  AS VARCHAR(4)) + '-'
				+ CAST(REPLACE(STR(@iMonth, 2), SPACE(1), '0')  AS VARCHAR(2)) + '-'
				+ CAST(REPLACE(STR(@iSeq, @iLength), SPACE(1), '0')  AS VARCHAR(20))
		SET @iSeq = @iSeq + 1;
		UPDATE GenerateNumber SET  [Sequence] = @iSeq WHERE [Year] = @iYear AND [Month] = @iMonth;
		
	END TRY
	BEGIN CATCH
		SET @sAutoID		=	@sPrefix + '-0000-00-00000';
	END CATCH

	RETURN 0;
END

DECLARE	@return_value int,
		@sAutoID varchar(30)

EXEC	@return_value = [dbo].[GetAutoIDNo]
		@sPrefix = N'TC',
		@iLength = 5,
		@sAutoID = @sAutoID OUTPUT

SELECT	@sAutoID as N'@sAutoID'