136.生成编码规则调整处理（T-SQL语句【函数】）

CREATE FUNCTION dbo.f_ChangeCodeRule(
@Old_CodeRule  varchar(50),  --以逗号分隔的旧的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
@New_CodeRule varchar(50),   --以逗号分隔的旧的编码规则,如果某个层次的编码长度为0,表示删除该层编码
@CharFill       char(1),     --扩充编码时,填充的字符
@Position       int,         --为0,从编码的最前面开始压缩或者填充,为-1或者大于旧编码的长度,从最后一位开始处理,为其他值,从指定的位置后开始处理
@FieldName     sysname       --编码字段名
)RETURNS nvarchar(4000)
AS
BEGIN
	IF ISNULL(@CharFill,'')='' SET @CharFill=N'0'
	DECLARE @old_Code TABLE(ID int IDENTITY,CodeLen int,CodeLens int,Code nvarchar(200))
	DECLARE @new_Code TABLE(ID int IDENTITY,CodeLen int)
	--插分旧编码规则到表
	DECLARE @CodeLen varchar(10),@CodeLens varchar(10)
	SET @CodeLens=1
	WHILE CHARINDEX(N',',@Old_CodeRule)>0
	BEGIN
		SELECT @CodeLen=LEFT(@Old_CodeRule,CHARINDEX(N',',@Old_CodeRule)-1),
			@Old_CodeRule=STUFF(@Old_CodeRule,1,CHARINDEX(N',',@Old_CodeRule),N'')
		INSERT @old_Code VALUES(@CodeLen,@CodeLens,N'SUBSTRING('+@FieldName+N','+@CodeLens+N','+@CodeLen+N')')
		SET @CodeLens=@CodeLens+CAST(@CodeLen as int)
	END
	INSERT @old_Code VALUES(@Old_CodeRule,@CodeLens,N'SUBSTRING('+@FieldName+N','+@CodeLens+N','+@Old_CodeRule+N')')

	--插分新编码规则到表
	WHILE CHARINDEX(N',',@New_CodeRule)>0
	BEGIN
		INSERT @new_Code VALUES(LEFT(@New_CodeRule,CHARINDEX(N',',@New_CodeRule)-1))
		SET @New_CodeRule=STUFF(@New_CodeRule,1,CHARINDEX(N',',@New_CodeRule),N'')
	END
	INSERT @new_Code VALUES(@New_CodeRule)

	--生成编号规则修改处理语句
	DECLARE @sql nvarchar(4000)
	SET @sql=''
	SELECT @sql=@sql
		+CASE 
			WHEN n.CodeLen=0 THEN ''                   --新编码长度为0,表示去掉这段编码
			ELSE N'+CASE WHEN LEN('+@FieldName
				+N')<'+CAST(o.CodeLens as varchar)
				+N' THEN '''' ELSE '+CASE
					WHEN n.CodeLen=o.CodeLen THEN N'+'+o.Code  --新旧编码长度相同时不需要处理
					WHEN n.CodeLen>o.CodeLen THEN CASE         --扩充编码长度的处理,根据@Position和旧编码长度决定编码的填充位置
						WHEN @Position=-1 OR @Position>=o.CodeLen
						THEN N'+'+o.Code
							+N'+'+QUOTENAME(REPLICATE(@CharFill,n.CodeLen-o.CodeLen),N'''')
						ELSE N'+STUFF('+o.Code
							+N','+CAST(@Position+1 as varchar)
							+N',0,'+QUOTENAME(REPLICATE(@CharFill,n.CodeLen-o.CodeLen),N'''')
							+N')'
						END
					ELSE CASE                                  --收缩编码长度的处理,根据@Position和新编码长度决定编码的截取位置
						WHEN @Position=-1 OR @Position>n.CodeLen
						THEN '+LEFT('+o.Code+N','+CAST(n.CodeLen as varchar)+N')'
						ELSE N'+STUFF('+o.Code
							+N','+CAST(@Position+1 as varchar)
							+N','+CAST(o.CodeLen-n.CodeLen as varchar)
							+N','''')'
						END
					END
				+N' END'
			END
	FROM @old_Code o,@new_Code n
	WHERE o.ID=n.ID
	RETURN(STUFF(@sql,1,1,N''))
END