看了楼上的代码,还是觉得非常复杂 (呵呵,兄弟是菜鸟,所以觉得复杂,不要见怪) sql server 2000 里面有没有递归函数呀. 要是想C一样有递归不就非常容易搞定了嘛
create procedure deletefolder(@m_name varchar(200)) as declare @m_id int declare @tmp1 table (m_id int,m_parent int) declare @tmp2 table (m_id int,m_parent int)select @m_id=(select m_id from films where m_name=@m_name)
insert @tmp1 select * from films where m_parent = @m_id /*循环的次数等于树的深度*/ while exists(select * from @tmp1) begin /*@tmp2表中存本次查询的层次的所有结点*/ insert @tmp2 select films.m_id,films.m_parent from films,@tmp1 where films.parent = @tmp1.m_id insert @tmp1 select * from @tmp2 delete from films where m_id in (select m_id from @tmp2) end delete from films where m_id=@m_id看看我写的代码,不知道为什么出错了? 服务器: 消息 137,级别 15,状态 2,过程 deletefolder,行 16 必须声明变量 '@tmp1'。
drop procedure deletefolder go create procedure deletefolder(@m_id int) asdeclare @tmp1 table (m_id int,m_parent int) declare @tmp2 table (m_id int,m_parent int)
insert @tmp1 select m_id,m_parent from films where m_parent = @m_id /*循环的次数等于树的深度*/ while exists(select * from @tmp1) begin /*@tmp2表中存本次查询的层次的所有结点*/ insert @tmp2 select films.m_id,films.m_parent from films, @tmp1 t where films.m_parent = t.m_id delete from @tmp1 insert @tmp1 select * from @tmp2 delete from films where m_id in (select m_id from @tmp2) delete from @tmp2 end delete from films where m_id=@m_id结果所有的都删除了,就是第二层没有删除. 我倒...
(呵呵,兄弟是菜鸟,所以觉得复杂,不要见怪)
sql server 2000 里面有没有递归函数呀.
要是想C一样有递归不就非常容易搞定了嘛
as
declare @m_id int
declare @tmp1 table (m_id int,m_parent int)
declare @tmp2 table (m_id int,m_parent int)select @m_id=(select m_id from films where m_name=@m_name)
insert @tmp1 select * from films where m_parent = @m_id
/*循环的次数等于树的深度*/
while exists(select * from @tmp1)
begin
/*@tmp2表中存本次查询的层次的所有结点*/
insert @tmp2 select films.m_id,films.m_parent from films,@tmp1 where films.parent = @tmp1.m_id
insert @tmp1 select * from @tmp2
delete from films where m_id in (select m_id from @tmp2)
end
delete from films where m_id=@m_id看看我写的代码,不知道为什么出错了?
服务器: 消息 137,级别 15,状态 2,过程 deletefolder,行 16
必须声明变量 '@tmp1'。
go
create procedure deletefolder(@m_id int)
asdeclare @tmp1 table (m_id int,m_parent int)
declare @tmp2 table (m_id int,m_parent int)
insert @tmp1 select m_id,m_parent from films where m_parent = @m_id
/*循环的次数等于树的深度*/
while exists(select * from @tmp1)
begin
/*@tmp2表中存本次查询的层次的所有结点*/
insert @tmp2 select films.m_id,films.m_parent from films, @tmp1 t where films.m_parent = t.m_id
delete from @tmp1
insert @tmp1 select * from @tmp2
delete from films where m_id in (select m_id from @tmp2)
delete from @tmp2
end
delete from films where m_id=@m_id结果所有的都删除了,就是第二层没有删除.
我倒...