ขอวิธีทำ Running Number แบบต่อ String ครับ คือผมเพิ่งเคยเขียนอ่ะครับ ผมรู้ว่ากระบวนการมันเป็นยังไง

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================================================
-- Author:		PROUD ,PNK.RJB U.
-- Create date: 02/12/2009
-- Description:	PREFIX STRING WITH SPECIFIC CHARACTOR
-- ==========================================================
ALTER FUNCTION [dbo].[LPAD] (@VAR_STR NVARCHAR(MAX) 
	,@VAR_LENGTH INT ,@VAR_FILL_CHAR NVARCHAR(1))
	RETURNS NVARCHAR(MAX)
AS
BEGIN
	
	DECLARE
		@TEMP			INT , 
		@COUNTER		INT ,
		@RETSTR			NVARCHAR(MAX) ;
		
	SET @TEMP = @VAR_LENGTH - LEN(@VAR_STR);
	SET @RETSTR = @VAR_STR ;
	IF (@TEMP>0)
	BEGIN
		SET @COUNTER = 0;
		WHILE (@COUNTER < @TEMP)
		BEGIN
			SET @RETSTR = @VAR_FILL_CHAR + @RETSTR ;
			SET @COUNTER = @COUNTER +1 ;
		END
	END	
					
	RETURN @RETSTR 

END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		PROUD ,PNK.RJB U.
-- Create date: 22/03/2010
-- Description:	GET NEW RUNNING NUMBER MAIN FUNCTION
-- =============================================
CREATE FUNCTION [dbo].[GET_NEW_RUNNING_NUMBER_EX]
(
	@VAR_YEAR					INT ,
	@VAR_DEPARTMENT				INT ,
	@VAR_DEPARTMENT_LENGTH		INT ,
	@VAR_COUNTER_LENGTH			INT ,
	@VAR_COUNTER_MAX_VALUE		INT  	
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	
	DECLARE 
		@DATA_YEAR						INT ,
		@DATA_DEPARTMENT				INT ,
		@DATA_COUNTER					INT , 
		@RET_STR						NVARCHAR(MAX) ,
		@CAP_ID							NVARCHAR(MAX) ,
		@TEMP_STR						NVARCHAR(MAX) ,
		@FOUNDED						INT ,
		@CONST_DEPAREMRNT_LENGTH		INT ,
		@CONST_COUNTER_LENGTH			INT ,
		@CONST_MAX_COUNTER				INT ;
		
	SET @CONST_DEPAREMRNT_LENGTH = @VAR_DEPARTMENT_LENGTH ;
	SET @CONST_COUNTER_LENGTH = @VAR_COUNTER_LENGTH ;	
	SET @CONST_MAX_COUNTER = @VAR_COUNTER_MAX_VALUE ;
		
	SET @DATA_YEAR = @VAR_YEAR ;
	SET @DATA_DEPARTMENT = @VAR_DEPARTMENT ;
	
	SET @CAP_ID = CONVERT(NVARCHAR(MAX) ,@DATA_YEAR) 
		+ dbo.LPAD(CONVERT( NVARCHAR(MAX) ,@DATA_DEPARTMENT ) 
			,@CONST_DEPAREMRNT_LENGTH ,'0') ;

	SET @TEMP_STR = @CAP_ID + '%'; 

	SELECT @RET_STR = ISNULL(MAX(Label),'')
		FROM _test_max_value WHERE Label LIKE @TEMP_STR;

	-- 4 debug 
	---SET @RET_STR = '201060123007' ;
	SET @DATA_COUNTER = 0 ;

	IF (LEN(@RET_STR) = (4+ @CONST_DEPAREMRNT_LENGTH + @CONST_COUNTER_LENGTH) ) 
	BEGIN
		SET @RET_STR = SUBSTRING(@RET_STR 
			,LEN(@TEMP_STR) ,LEN(@RET_STR) - LEN(@CAP_ID)+1 );
		
		SET @DATA_COUNTER = CONVERT(INT ,@RET_STR) ;
	END
		
	SET @DATA_COUNTER = @DATA_COUNTER + 1 ;	
	-- Check with maximum value
	IF (@DATA_COUNTER > @CONST_MAX_COUNTER )
	BEGIN
		SET @DATA_COUNTER = 1 ;
	END

	-- Compose ID
	SET @RET_STR = @CAP_ID 
		+ dbo.LPAD(CONVERT(NVARCHAR(MAX) ,@DATA_COUNTER)
			,@CONST_COUNTER_LENGTH ,'0');
		
	
	RETURN @RET_STR

END

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =====================================================================
-- Author:		PROUD ,PNK.RJB U.
-- Create date: 22/03/2010
-- Description:	GET NEW RUNNING NUMBER SHORT VERSION > EMULATE C# OVERLOAD
-- =====================================================================
CREATE FUNCTION [dbo].[GET_NEW_RUNNING_NUMBER]
(
	@VAR_YEAR					INT ,
	@VAR_DEPARTMENT				INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	
	RETURN dbo.GET_NEW_RUNNING_NUMBER_EX
		( @VAR_YEAR ,@VAR_DEPARTMENT ,3 ,5 ,99999)

END

GO