單號產生器

出自ProgWiki

用途

• 根據現在日期（取西元年月日各2碼），依流水號計算出單號。（可根據實際需求，略加修改下列之MS-SQL預存函數）

程式碼

CREATE FUNCTION [dbo].[GetNewContractNo]
(
)
RETURNS VARCHAR(12)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar AS VARCHAR(12);
	DECLARE @now AS DATETIME;
	DECLARE @IDHeader AS VARCHAR(50);
	DECLARE @WhereLike AS VARCHAR(50);
 
	SET @now = GETDATE();
 
	SET @IDHeader = CONVERT(VARCHAR,(@now),112);
--SET @IDHeader='C080624';
	SET @IDHeader = 'C' + SUBSTRING(@IDHeader, 3, LEN(@IDHeader)-2);
--select @IDHeader;
 
	SET @WhereLike = @IDHeader + '%';
 
	SELECT @ResultVar = MAX([ContractNo]) FROM [Contract] WHERE [ContractNo] like @WhereLike;
--	SELECT @ResultVar
 
	IF (@ResultVar IS NULL) 
		BEGIN
                        --當流水號長度為2時, 當天第1張單號的預設值
			SET @ResultVar = @IDHeader + '01';
		END
	ELSE
		BEGIN
                        --這裡的2是流水號長度
			SET @ResultVar = CONVERT(VARCHAR(2),CONVERT(INT,SUBSTRING(@ResultVar,LEN(@IDHeader)+1,2))+1);
			WHILE (LEN(@ResultVar) < 2)
				BEGIN
					SET @ResultVar = '0' + @ResultVar;
				END
			SET @ResultVar = @IDHeader + @ResultVar; 
		END
 
 
--	SELECT @ResultVar;
 
	-- Return the result of the function
	RETURN @ResultVar
END