create procedure d_selt2
@pid int
as
select a.name as 文章分类名,b.title as 标题 from t1 a join t2 on a.bid=b.bid and a.pid=@pid
goexec d_selt2 1
@pid int
as
select a.name as 文章分类名,b.title as 标题 from t1 a join t2 on a.bid=b.bid and a.pid=@pid
goexec d_selt2 1
declare @N int
set @N = 1
begin tran
select t1.name, t2.title from t1 inner join t2 on t1.bid = t2.bid where t1.pid = @N
commit tran
如果这两张表存在的话，直接运行以上SQL便可以得出结果
as
begin
--select
select a.name as 文单分类名,b.title as 标题 from t1 a join t2 b on b.bid=a.bid
where a.pid=@pid
end
create proc p_qry
@Bid int=0,
@N int=0 --0表示不限制
as
declare @t table(Bid int,level int)
declare @l int
set @l=0
insert @t select bid,@l from t1 where Pid=@Bid
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.bid,@l
from t1 a join @t b on a.pid=b.bid and b.level=@l-1
endset rowcount @N
select 文章分类=a.name,标题=b.Title
from @t ab
join t1 a on a.bid=ab.bid
join t2 b on b.bid=ab.bid
set rowcount 0
go--调用
exec p_qry 1,3
create table t1(Bid int,Name varchar(10),Pid int)
insert t1 select 1,'电脑技术',0
union all select 2,'软件' ,1
union all select 3,'硬件' ,1
union all select 4,'网络技术',0
union all select 5,'互联网' ,4
union all select 6,'局域网' ,4create table t2(Aid int,Title varchar(10),Bid int)
insert t2 select 1,'abccc' ,2
union all select 2,'afdfaf' ,2
union all select 3,'eafdda' ,3
union all select 4,'edfdfsa' ,5
union all select 5,'dfadfafd',5
union all select 6,'dfddfdaf',6
go--查询的存储过程
create proc p_qry
@Bid int=0,
@N int=0 --0表示不限制
as
declare @t table(Bid int,level int)
declare @l int
set @l=0
insert @t select bid,@l from t1 where Pid=@Bid
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.bid,@l
from t1 a join @t b on a.pid=b.bid and b.level=@l-1
endset rowcount @N
select 文章分类=a.name,标题=b.Title
from @t ab
join t1 a on a.bid=ab.bid
join t2 b on b.bid=ab.bid
set rowcount 0
go--调用
exec p_qry 1,3
go--删除测试
drop table t1,t2
drop proc p_qry/*--测试结果文章分类 标题
---------- ----------
软件 abccc
软件 afdfaf
硬件 eafdda（所影响的行数为 3 行）
--*/