Function Return Table

--Function Return Table

create function f_cid(
@ClassName varchar(10) --要查询的类别名(如果类别名会重复,改用NClassID
)returns @re table(NClassID int,level int)
as
begin
 declare @l int
 set @l=0
 insert @re select NClassID,@l
 from Class
 where ClassName=@ClassName
 while @@rowcount>0
 begin
  set @l=@l+1
  insert @re select a.NClassID,@l
  from Class a,@re b
  where a.ClassID=b.NClassID and b.level=@l-1
 end
 return
end
go

--调用函数实现查询
select a.* ,b.ArticleID,b.Title
from Class a,Article b,f_cid('体育') a1 --查询体育及下面的所有子类
where a.NClassID=a1.NClassID  and b.NClassID=a1.NClassID
      and b.ArticleID in( select top 10 ArticleID from Article
                     where NClassID=a1.NClassID)