CREATE PROC p_VerifyData
@TableName sysname, --要校验树形数据的表
@CodeField sysname, --编码字段名
@ParentCodeField sysname --上级编码字段名
AS
SET NOCOUNT ON
--参数检查
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=@CodeField
)
BEGIN
RAISERROR(N'列"%s"在用户表"%s"中不存在',1,16,@CodeField,@TableName)
RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND
name=@ParentCodeField
)
BEGIN
RAISERROR(N'列"%s"在用户表"%s"中不存在',1,16,@ParentCodeField,@TableName)
RETURN
END
SELECT @TableName=QUOTENAME(@TableName),
@CodeField=QUOTENAME(@CodeField),
@ParentCodeField=QUOTENAME(@ParentCodeField)
--数据检查
EXEC(N'
--检查导致循环的节点
DECLARE @Level int
SET @Level=1
SELECT ID,PID,Path=CAST(ID as varchar(8000)),Level=@Level
INTO # FROM(--列出所有父节点不是根节点的数据(使用子查询是防止编码列为IDENTITY列时,导致后面的插入处理出错)
SELECT
ID=a.'+@CodeField+N',PID=a.'+@ParentCodeField+N'
FROM
'+@TableName+N'
a,'+@TableName+N' b
WHERE
a.'+@ParentCodeField+N'=b.'+@CodeField+N'
AND
b.'+@ParentCodeField+N'
IS NOT NULL)a
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT # SELECT
a.'+@CodeField+N',b.PID
,
CAST(
a.'+@CodeField+N'
as varchar(8000))+''>''+b.Path,@Level
FROM
'+@TableName+N'
a,# b
WHERE
a.'+@ParentCodeField+N'=b.ID
AND
b.Level=@Level-1
AND b.ID<>b.PID
END
--显示结果
SELECT
'+@CodeField+N',Description=N''
父节点无效''
FROM
'+@TableName+N'
a
WHERE
'+@ParentCodeField+N'
IS NOT NULL
AND NOT EXISTS(
SELECT * FROM
'+@TableName+N'
WHERE
'+@CodeField+N'=a.'+@ParentCodeField+N'
)
UNION ALL --显示产生循环的节点
SELECT ID,N''循环:''+Path+''>''+CAST(ID as varchar(8000))
FROM # WHERE ID=PID
')
