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

很好的sql 文档,可能邦你成为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')