删除节点处理的通用存储过程.sql

CREATE PROC p_DeleteCode
@TableName sysname,         --调整编码规则的表名
@FieldName sysname,         --编码字段名
@CodeRule varchar(50),     --以逗号分隔的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
@Code     varchar(50)       --要删除的节点编码
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 @Pos1 int,@Pos2 int,@CodeLens int,
   @New_CodeRule varchar(50),@s nvarchar(4000),
   @Code1 varchar(100),@Code2 varchar(100)
SELECT @Pos1=1,
   @Pos2=CHARINDEX(N',',@CodeRule+N',',@Pos1),
   @CodeLens=SUBSTRING(@CodeRule,@Pos1,@Pos2-@Pos1)
WHILE @Pos2>=0 AND @CodeLens<LEN(@Code)
   SELECT @Pos1=@Pos2+1,
       @Pos2=CHARINDEX(N',',@CodeRule+N',',@Pos1),
       @CodeLens=@CodeLens+SUBSTRING(@CodeRule,@Pos1,@Pos2-@Pos1)
IF @CodeLens<>LEN(@Code)
BEGIN
   RAISERROR(N'编码"%s"不符合指定的编码规则"%s"',1,16,@Code,@CodeRule)
   RETURN
END
SELECT @New_CodeRule=STUFF(@CodeRule,@Pos1,0,N'0,'),
   @TableName=QUOTENAME(@TableName),
   @FieldName=QUOTENAME(@FieldName),
   @s=dbo.f_ChangeCodeRule(@CodeRule,@New_CodeRule,'',0,@FieldName),
   @Code1=QUOTENAME(@Code+N'_%',N''''),
   @Code2=QUOTENAME(@Code,N'''')

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

--检查更新后的编码是否存在重复
IF EXISTS(SELECT New_No FROM # GROUP BY New_No HAVING COUNT(*)>1)
   SELECT * FROM # a
   WHERE EXISTS(
       SELECT * FROM # WHERE New_No=a.New_No AND Old_No<>a.Old_No)
   ORDER BY New_No,Old_No
ELSE
--检查更新后的编码是否与表中现有的编码重复
IF EXISTS(SELECT * FROM '+@TableName+N' a,# b
   WHERE a.'+@FieldName+N'<>'+@Code2+N'
       AND a.'+@FieldName+N'=b.New_No
       AND a.'+@FieldName+N'<>b.Old_No)
   SELECT a.'+@FieldName+N',b.*
   FROM '+@TableName+N' a,# b
   WHERE a.'+@FieldName+N'<>'+@Code2+N'
       AND a.'+@FieldName+N'=b.New_No
       AND a.'+@FieldName+N'<>b.Old_No
ELSE
   --如果编码处理后不重复,则更新到编码表中
   DELETE FROM '+@TableName+N'
   WHERE '+@FieldName+N'='+@Code2+N'
   UPDATE a SET '+@FieldName+N'=b.New_No
   FROM '+@TableName+N' a,# b
   WHERE a.'+@FieldName+N'=b.Old_No
COMMIT TRAN')

chaunceyhao