执行存储过程结果是:
12 14 11 13 18 17 15 16 还是:
id号 parent_id 是否有子类 排序 超连接 名称
id parend_id next_id id_order url content
15 11 0 2 http://31ec.com15 15
14 12 0 1 http://31ec.com14 14
13 11 1 1 http://31ec.com13 13
12 0 1 1 http://31ec.com12 12
11 0 1 2 http://31ec.com11 11
16 11 0 3 http://31ec.com16 16
17 13 0 2 http://31ec.com17 17
18 13 0 1 http://31ec.com17 18
12 14 11 13 18 17 15 16 还是:
id号 parent_id 是否有子类 排序 超连接 名称
id parend_id next_id id_order url content
15 11 0 2 http://31ec.com15 15
14 12 0 1 http://31ec.com14 14
13 11 1 1 http://31ec.com13 13
12 0 1 1 http://31ec.com12 12
11 0 1 2 http://31ec.com11 11
16 11 0 3 http://31ec.com16 16
17 13 0 2 http://31ec.com17 17
18 13 0 1 http://31ec.com17 18
menu(0),0是输入参数--parent_id
执行存储过程结果是:
12 14 11 13 18 17 15 16
id号 parent_id 排序
是否有子类 超连接 名称
id parend_id id_order content
next_id url
15 11 0 2 http://31ec.com15 15
14 12 0 1 http://31ec.com14 14
13 11 1 1 http://31ec.com13 13
12 0 1 1 http://31ec.com12 12
11 0 1 2 http://31ec.com11 11
16 11 0 3 http://31ec.com16 16
17 13 0 2 http://31ec.com17 17
18 13 0 1 http://31ec.com17 18
==============================
伪代码pro menu(id)-------------存储过程
str
funtion menu1(id)----------自定义函数
rs=select * from menu where parent_id="&id&" order by id_order
得到结果集,然后利用游标
把读第一个记录,取出记录得rs(next_id)值,如果是1,调用menu1(rs(id))
如果值为0,str=str+rs(name)
end funtion
return str希望得到得str=12 14 11 13 18 17 15 16 但是真得要我编写得时候就老出错误,希望邹兄费神了,多谢
是否有子类 超连接 名称
id parend_id id_order content
next_id url
15 11 0 2 http://31ec.com15 15
14 12 0 1 http://31ec.com14 14
13 11 1 1 http://31ec.com13 13
12 0 1 1 http://31ec.com12 12
11 0 1 2 http://31ec.com11 11
16 11 0 3 http://31ec.com16 16
17 13 0 2 http://31ec.com17 17
18 13 0 1 http://31ec.com17 18
create function f_menu(
@id int
)returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s='' declare @next_id int,@name varchar(20) declare tb cursor local for
select id,next_id,name from menu
where parent_id=@id open tb
fetch tb into @id,@next_id,@name
while @@fetch_status=0
begin
set @s=@s+' '+case @next_id
when 1 then dbo.f_menu(@id)
else @name end
fetch tb into @id,@next_id,@name
end
close tb
deallocate tb
return(stuff(@s,1,1,''))
end
go--调用
select dbo.f_menu(15)
/****** Object: Table [dbo].[menu] Script Date: 2004-7-7 14:15:29 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[menu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[menu]
GO/****** Object: User dbo Script Date: 2004-7-7 14:15:27 ******/
/****** Object: Table [dbo].[menu] Script Date: 2004-7-7 14:15:30 ******/
CREATE TABLE [dbo].[menu] (
[id] [int] NOT NULL ,
[parent_id] [int] NULL ,
[next_id] [bit] NULL ,
[id_order] [int] NULL ,
[url] [varchar] (50) NULL ,
[menuname] [varchar] (50) NULL
) ON [PRIMARY]
GOinsert into menu values('15','11','0','2','http://31ec.com15','15')
insert into menu values('14','12','0','1','http://31ec.com14','14')
insert into menu values('13','11','1','1','http://31ec.com13','13')
insert into menu values('12','0','1','1','http://31ec.com15','12')
insert into menu values('11','0','1','2','http://31ec.com15','11')
insert into menu values('16','11','0','3','http://31ec.com15','16')
insert into menu values('17','13','0','2','http://31ec.com15','17')
insert into menu values('18','13','1','1','http://31ec.com15','18')
insert into menu values('19','18','0','1','http://31ec.com19','19')
=============================
Alter function f_menu(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str='' declare @next_id int,@menuname varchar(20) declare menu cursor local for
select id,next_id,menuname from menu
where parent_id=@id order by id_order open menu
fetch menu into @id,@next_id,@menuname
while @@fetch_status=0
begin
set @str=@str+str(@menuname)+' ' if @next_id=1 dbo.f_menu(@id)
----这里改一下,出错了(上面我写得伪代码有误,)
fetch menu into @id,@next_id,@menuname
end
close menu
deallocate menu
return(@str)
end
go
select dbo.f_menu(0)还有用你原来得代码,为什么每个数字之间间距很大!!谢谢
不明白你想改成什么?
strfuntion menu1(id)----------自定义函数
rs=select * from menu where parent_id="&id&" order by id_order
得到结果集,然后利用游标 if 记录存在 then
移到记录第一条
当还没到最后一条记录
把读第一个记录,str=str+rs(menuname)
取出记录得rs(next_id)值
if rs(next_id)=1 then 调用menu1(rs(id))end if
读取下一个记录
循环
endif
end funtionreturn str
@id int
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
declare @next_id int,@menuname varchar(100) declare tb cursor local for
select * from menu
where parent_id=@id
order by id_order open tb
fetch tb into @id,@next_id,@menuname
while @@fetch_status=0
begin
set @re=@re+@menuname --这里按你的逻辑,好像有点问题吧?
if @next_id=1
set @re=@re+' '+dbo.f_menu(@id)
fetch tb into @id,@next_id,@menuname
end
close tb
deallocate tb
return(stuff(@re,1,1,''))
end
go
Alter function f_menu(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str='' declare @next_id int,@menuname varchar(20) declare menu cursor local for
select id,next_id,menuname from menu
where parent_id=@id order by id_order open menu
fetch menu into @id,@next_id,@menuname
while @@fetch_status=0
begin
set @str=@str+str(@menuname) --这里按你的逻辑,好像有点问题吧?
if @next_id=1
set @str=@str+dbo.f_menu(@id)
fetch menu into @id,@next_id,@menuname
end
close menu
deallocate menu
return(stuff(@str,1,1,''))
end
go
...
while @@fetch_status=0
begin
if @next_id=1
set @str=@str+dbo.f_menu(@id)
else
set @str=@str+str(@menuname)
fetch menu into @id,@next_id,@menuname
....
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str='' declare @next_id int,@menuname varchar(20) declare menu cursor local for
select id,next_id,menuname from menu
where parent_id=@id order by id_order open menu
fetch menu into @id,@next_id,@menuname
while @@fetch_status=0
begin
set @str=@str+'abc'+str(@menuname)+'cd' --这里按你的逻辑,好像有点问题吧?
if @next_id=1
set @str=@str+'ef'+dbo.f_menu(@id)
fetch menu into @id,@next_id,@menuname
end
close menu
deallocate menu
return(stuff(@str,1,1,''))
end
go
结果这是搞笑哦
bc 12cdefbc 14cdabc 11cdefbc 13cdefbc 18cdefbc 19cdabc 17cdabc 15cdabc 16cd
alter PROCEDURE menu_tree
@id int
AS
select dbo.f_menu(@id)
GO
Alter function f_menu(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str='' declare @next_id int,@menuname varchar(100) declare menu cursor local for
select id,next_id,menuname from menu
where parent_id=@id order by id_order open menu
fetch menu into @id,@next_id,@menuname
while @@fetch_status=0
begin
set @str=@str+@menuname
if @next_id=1
set @str=@str+dbo.f_menu(@id)
fetch menu into @id,@next_id,@menuname
end
close menu
deallocate menu
return(@str)
end
go
这是区别