移动节点处理的通用存储过程.sql

CREATE PROC p_Move_CopyCode
@TableName sysname,        --调整编码规则的表名
@FieldName sysname,        --编码字段名
@CodeRule   varchar(50),    --以逗号分隔的编码规则，每层编码的长度，比如1,2,3,表示有三层编码，第一层长度为1，第二层长度为2，第三层长度为3
@Code      varchar(50),     --要复制或者移动的节点编码
@ParentCode varchar(50),    --移动到该编码的节点下
@IsCopy    bit=0            --0为移动处理,否则为复制处理
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
IF ISNULL(@Code,'')='' RETURN


--生成编码规则修改字符串
DECLARE @CodeLen int,@CodeLens varchar(10),@Pos varchar(10),
   @Old_CodeRule varchar(50),@New_CodeRule varchar(50),
   @s nvarchar(4000),
   @Code1 varchar(100),@Code2 varchar(100)

IF ISNULL(@ParentCode,'')=''
   SELECT @ParentCode=N'',
       @New_CodeRule=@CodeRule
SET @CodeLens=0
WHILE CHARINDEX(N',',@CodeRule)>0
BEGIN
   SET @CodeLen=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule)-1)
   IF @CodeLens+@CodeLen=LEN(@Code)
   BEGIN
       SELECT @Old_CodeRule=@CodeRule,
           @Pos=@CodeLens
       IF @New_CodeRule>'' GOTO lb_CalcCodeLens
   END
   SELECT @CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule),N''),
       @CodeLens=@CodeLens+@CodeLen
   IF @CodeLens=LEN(@ParentCode)
   BEGIN
       SET @New_CodeRule=@CodeRule
       IF @Old_CodeRule>'' GOTO lb_CalcCodeLens
   END
END
IF @Old_CodeRule IS NULL AND CAST(@CodeLens as int)+@CodeRule=LEN(@Code)
   SELECT @Old_CodeRule=@CodeRule,
           @Pos=@CodeLens
IF @New_CodeRule IS NULL AND CAST(@CodeLens as int)+@CodeRule=LEN(@ParentCode)
BEGIN
   RAISERROR(N'移动编码"%s"到编码"%s"下导致编码长度溢出编码规则允许的长度',1,16,@Code,@ParentCode)
   RETURN
END

lb_CalcCodeLens:
SET @s=N'SET @CodeLens=@CodeLens+'+REPLACE(@CodeRule,N',',N'+')
EXEC sp_executesql @s,N'@CodeLens int OUTPUT',@CodeLens OUTPUT

IF @Old_CodeRule IS NULL
BEGIN
   RAISERROR(N'编码"%s"不符合指定的编码规则',1,16,@Code)
   RETURN
END
IF @New_CodeRule IS NULL
BEGIN
   RAISERROR(N'编码"%s"不符合指定的编码规则',1,16,@ParentCode)
   RETURN
END

DECLARE @Parent_Chk nvarchar(4000),@Delete_old nvarchar(4000),@where nvarchar(4000)
SELECT @TableName=QUOTENAME(@TableName),
   @FieldName=QUOTENAME(@FieldName),
   @s=CASE
       WHEN @Old_CodeRule=@New_CodeRule THEN N'New_no'
       ELSE dbo.f_ChangeCodeRule(@Old_CodeRule,@New_CodeRule,'',0,N'New_No')
   END,
   @Code1=QUOTENAME(@Code+N'%',N''''),
   @Code2=QUOTENAME(@Code,N''''),
   @ParentCode=QUOTENAME(@ParentCode,N''''),
   @Parent_Chk=CASE
       WHEN @ParentCode=N'''''' THEN N''
       ELSE 'IF NOT EXISTS(SELECT * FROM '+@TableName
           +N' WHERE '+@FieldName+N'='+@ParentCode+N')
   BEGIN
       RAISERROR(N''编码"%s"不存在'',1,16,'+@ParentCode+N')
       ROLLBACK TRAN
       RETURN
   END' END,
   @Delete_old=CASE
       WHEN @IsCopy=0
       THEN N'DELETE a FROM '+@TableName
           +N' a,# b WHERE a.'+@FieldName+N'=b.Old_No'
       ELSE N'' END,
   @where=CASE
       WHEN @IsCopy=0 THEN N'AND Old_No<>a.Old_No'
       ELSE N'' END

--检查并完成删除处理
EXEC(N'BEGIN TRAN
'+@Parent_Chk+N'
--将处理后的编码与处理前的编码保存到临时表
SELECT Old_No,New_No='+@ParentCode+N'+'+@s+N'
INTO # FROM(
   SELECT Old_No='+@FieldName+N',
       New_No=STUFF('+@FieldName+N',1,'+@Pos+N','''')
   FROM '+@TableName+N' WITH(XLOCK,TABLOCK)
   WHERE '+@FieldName+N' LIKE '+@Code1+N')a

--编码重复检测
SELECT Err=N''超过编码规则能处理的长度'',* INTO #1 FROM # WHERE LEN(New_No)>'+@CodeLens+N'
UNION ALL
SELECT Err=N''转换后编码重复'',* FROM # a
WHERE EXISTS(
   SELECT * FROM # WHERE New_No=a.New_No AND Old_No<>a.Old_No)
UNION ALL
SELECT Err=N''转换后与表中现有的编码重复'',* FROM # a
WHERE EXISTS(
   SELECT * FROM '+@TableName+N'
   WHERE '+@FieldName+N'=a.New_No '+@where+N')
IF @@ROWCOUNT>0
   SELECT * FROM #1 ORDER BY Err,New_No
ELSE
BEGIN
   --移动编码
   SELECT a.* INTO #2
   FROM '+@TableName+N' a,# b
   WHERE a.'+@FieldName+N'=b.Old_No
   UPDATE a SET '+@FieldName+N'=b.New_No
   FROM #2 a,# b
   WHERE a.'+@FieldName+N'=b.Old_No
'+@Delete_old+N' --如果是移动,先进行删除处理
   INSERT '+@TableName+N' SELECT * FROM #2
END
COMMIT TRAN')

chaunceyhao