移动节点处理的通用存储过程.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')