單號產生器

出自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