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
')

校驗表中數據是否有循環編碼的通用存儲過程.sql