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

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 @CodeLensLEN(@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')