递归查指定节点的所有及联子节点
CREATE PROC USP_CHECKINUSED
@id int
AS
--定义一个表变量
DECLARE @t TABLE(id int)
--首先查出直接的子节点
INSERT @t
SELECT id
FROM SanitStuffSort
WHERE pid = @id
--做一个循环依次查出下一级的子节点所属的子节点
WHILE @@rowcount > 0
INSERT @t
SELECT a.id
FROM SanitStuffSort AS a INNER
JOIN @t AS b
ON a.pid = b.id
AND a.id NOT IN (SELECT id FROM @t)
--与业务进行关联,验证是否存在使用
SELECT id
FROM @t
WHERE id IN (SELECT sortno FROM SanitStuffItem)
--------------------------------------------------------------------