请教一个SQL的嵌套存储过程问题，请帮忙啊！！！
假定我们用关系SEQ(from, to)来存储序列，比如对于序列ABCD，在SEQ中存储为(A,B), (B,C)，(C,D)，(D,null)。定义存储过程show_seq(start)，给出从start出发的整个序列。比如show_seq(B)，输出B, C, D。可以用嵌套存储过程来实现
[解决办法]
LZ用的是第歸的方法,受32層限制,可以改用其他方法.
比如function,或者直接寫proc
另外,default null不能加 ' '的,不然變字串了
1.--function
CREATE TABLE SEQ(
[from] [char] (10) default null ,
[to] [char] (10) default null
)
insert into SEQ values( 'A ', 'B ')
insert into SEQ values( 'B ', 'C ')
insert into SEQ values( 'C ', 'D ')
insert into SEQ values( 'D ',null)
Go
CREATE function f_cid(@from varchar(10))
returns @t_level table([from] varchar(10),level int)
as
begin
declare @level int
set @level=1
insert into @t_level select @from,@level
while @@rowcount> 0
begin
set @level=@level+1
insert into @t_level select a.[to],@level
from SEQ a,@t_level b
where a.[from]=b.[from]
and b.level=@level-1
end
return
end
GO
--查詢語句:
select a.[from] from dbo.f_cid( 'B ') a
where a.[from] is not null
/*
from
----------
B
C
D
*/
[解决办法]
CREATE TABLE SEQ(
[from] [char] (10) default 'null ' ,
[to] [char] (10) default 'null '
)
insert into SEQ values( 'A ', 'B ')
insert into SEQ values( 'B ', 'C ')
insert into SEQ values( 'C ', 'D ')
insert into SEQ values( 'D ', 'null ')
go
create proc show_seq(@start varchar(10))
as
set nocount on
declare @answer varchar(10)
select @answer=[from],@start=[to] from SEQ where [from]=@start
if @answer is not null
begin
print @answer
exec show_seq @start
end
else
print '结束 '
go
exec show_seq 'B '
drop table seq
drop proc show_seq
