--重排编码的存储过程
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')
