一个sql递归查询子节点的存储过程

create proc spGetChildren @id int as     declare @t table(id int)     insert @t select id from newsclass where fatherid = @id     while @@rowcount > 0         insert @t select a.id from newsclass as a inner join @t as b         on a.fatherid = b.id and a.id not in(select id from @t)     select * from @t GO

 

另:sql2005下用一条公用表表达式(CTE) 查询语句也可以实现了:

;WITH TREE AS(     SELECT * FROM newsclass     WHERE fatherid = 3  -- 要查询的父 id     UNION ALL     SELECT T.* FROM newsclass T, TREE     WHERE T.fatherid = TREE.id ) SELECT * FROM TREE