我的程序:
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')
create proc show_seq @start varchar(10)
as
declare @answer varchar(10)
select @answer=[to] from SEQ where [from]=@startif (@answer=null)
return -1
else
exec @answer=show_seq @answer
return(@answer)
运行后
exec show_seq 'B'
出现嵌套层数超过32次,别人告诉我可能有死循环,可我找不到
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')
create proc show_seq @start varchar(10)
as
declare @answer varchar(10)
select @answer=[to] from SEQ where [from]=@startif (@answer=null)
return -1
else
exec @answer=show_seq @answer
return(@answer)
运行后
exec show_seq 'B'
出现嵌套层数超过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
endGO
--查詢語句:
select a.[from] from dbo.f_cid('B') a
where a.[from] is not null
/*
from
----------
B
C
D
*/
[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
declare @answer varchar(10)
select @answer=[from],@start=[to] from SEQ where [from]=@startif @answer is not null
begin
print @answer
exec show_seq @start
end
else
print '结束'
go
exec show_seq 'B'
[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]=@startif @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
if (@answer=null) ---不能这样, 应该是if (@answer is null)
另外一楼说了你的null不要括号
不过这样改好,你得到的始终是-1没有意义,应该用输出参数