133.树形数据编号重排的通用存储过程

比眉伴天荒 2024-02-19 19:08 65阅读 0赞

--重排编码的存储过程
    CREATE PROC p_RTaxisCode
    @TableName sysname,    --重排编码的表名
    @FieldName sysname,    --编码字段名
    @CodeRule varchar(100)  --以逗号分隔的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
    AS
    --参数检查
    IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
    BEGIN
    	RAISERROR(N'"%s"不存在,或者不是用户表',1,16,@TableName)
    	RETURN
    END
    IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@FieldName)
    BEGIN
    	RAISERROR(N'列名"%s"在用户表"%s"中无效',1,16,@FieldName,@TableName)
    	RETURN	
    END
    IF ISNULL(@CodeRule,'')=''
    BEGIN
    	RAISERROR(N'必须编码规则字符串',1,16)
    	RETURN	
    END
    IF PATINDEX(N'%[^0-9^,]%',@CodeRule)>0
    BEGIN
    	RAISERROR(N'编码规则字符串"%s"中只能包含数字和逗号(,)',1,16,@CodeRule)
    	RETURN	
    END
    
    --生成编码重排处理语句
    DECLARE @s nvarchar(4000),@len int,@lens int
    SELECT 
    	@TableName=QUOTENAME(@TableName),
    	@FieldName=QUOTENAME(@FieldName),
    	@len=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule+N',')-1),
    	@lens=@len,
    	@CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule+N','),N'')+N',',
    	@s=N'RIGHT('+CAST(POWER(10,@len) as varchar)
    		+N'+(SELECT COUNT(DISTINCT '+@FieldName
    		+N') FROM '+@TableName
    		+N' WHERE '+@FieldName+N'<=a.'+@FieldName
    		+N' AND LEN('+@FieldName+N')='+CAST(@len as varchar)
    		+N'),'+CAST(@len as varchar)+N')'
    
    WHILE LEN(@CodeRule)>1
    BEGIN
    	SELECT 
    		@len=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule)-1),
    		@s=@s
    			+N'+CASE WHEN LEN('+@FieldName
    			+N')>'+CAST(@lens as varchar)
    			+N' THEN RIGHT('+CAST(POWER(10,@len) as varchar)
    			+N'+(SELECT COUNT(DISTINCT '+@FieldName
    			+N') FROM '+@TableName
    			+N' WHERE '+@FieldName+N'<=a.'+@FieldName
    			+N' AND '+@FieldName+N' LIKE LEFT(a.'+@FieldName
    			+N','+CAST(@lens as varchar)
    			+N')+'''+REPLICATE(N'_',@len)
    			+N'''),'+CAST(@len as varchar)
    			+N') ELSE '''' END',
    		@lens=@lens+@len,
    		@CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule),N'')
    END
    EXEC(N'UPDATE a SET '+@FieldName+N'='+@s+N' FROM '+@TableName+N' a')