我又A B两个子段 是包含关系
A B
d z
z x
z y
x a
y b
现在我往另个表里插一条数据 ,希望这条数据所包含的数据能自动插入
CREATE PROCEDURE aaaaa
@box_d char(10),
@zai char(10)
AS
declare @count int
set @count=1
while @count<>0
begin
declare @tb table (box_x char(10))
insert into @tb(box_x) select box_x from l_box_box where box_d in (@box_d)
declare @box char(10)
select @count =count(*) from @tb
set @box_d=''
declare @row int
set @row=0
while @row<=@count-1
begin
select @box=(select top 1 box_x from @tb )
insert l_zai_box(box,zai) values (@box,@zai)
delete @tb where box_x=@box
set @row=@row+1
set @box_d=@box_d+','+@box
end
end
GO
这是我写的 不过有问题 希望大家帮下忙看下
A B
d z
z x
z y
x a
y b
现在我往另个表里插一条数据 ,希望这条数据所包含的数据能自动插入
CREATE PROCEDURE aaaaa
@box_d char(10),
@zai char(10)
AS
declare @count int
set @count=1
while @count<>0
begin
declare @tb table (box_x char(10))
insert into @tb(box_x) select box_x from l_box_box where box_d in (@box_d)
declare @box char(10)
select @count =count(*) from @tb
set @box_d=''
declare @row int
set @row=0
while @row<=@count-1
begin
select @box=(select top 1 box_x from @tb )
insert l_zai_box(box,zai) values (@box,@zai)
delete @tb where box_x=@box
set @row=@row+1
set @box_d=@box_d+','+@box
end
end
GO
这是我写的 不过有问题 希望大家帮下忙看下
另各表里的数据是
A B
z h
x h
y h
a h
b h
AS
begin
declare @re table(NodeName int,grade int)
declare @grade int
set @grade=0
insert @re select @NodeName,@grade
while @@rowcount>0
begin
set @grade=@grade+1
insert @re select a.A,@grade from TableA as a,@re as b where a.B=b.NodeName and b.level=@grade-1
end
create table TableB(A char(10),B char(10)) --创建你要插入的表,如果有就可以不用这句
insert into TableB select a.NodeName,@zai from @re a where a.grade<(select grade from @re where NodeName=@box_d)
and a.grade>(select grade from @re where NodeName=@zai)end
create proc aaaaa(@box_d varchar(10),@zai varchar(10))
AS
begin
declare @re table(NodeName varchar(10),grade int)
declare @grade int
set @grade=0
insert into @re select 'd',@grade
while @@rowcount>0
begin
set @grade=@grade+1
insert into @re select a.b,@grade from TableA as a,@re as b where a.a=b.NodeName and b.grade=@grade-1
end
select * from @re
create table TableB(A char(10),B char(10)) --创建你要插入的表,如果有就可以不用这句
insert into TableB select a.NodeName,@zai from @re a where a.grade>=(select grade from @re where NodeName=@box_d)
and a.grade<=(select grade from @re where NodeName=@zai)endcreate table tableA(A char(10),B char(10))
insert into tablea select 'd','z'
union select 'z','x'
union select 'z','y'
union select 'x','a'
union select 'y','b'
select * from tablea
exec aaaaa 'd','b'
select * from TableB
drop table tableb
返回受上一语句影响的行数
我给你的OK了?
@box_d varchar(10),
@zai varchar(10)
AS
begin
declare @re table(NodeName varchar(10),grade int)
declare @grade int
set @grade=0
insert into @re select @box_d,@grade
while @@rowcount>0
begin
set @grade=@grade+1
insert into @re select a.box_x,@grade from l_box_box as a,@re as b where a.box_d=b.NodeName and b.grade=@grade-1
end我这样写 好像有错
改为
create proc test--名字不能数字开头
(--多个参数用括号括起来
@box_d varchar(10),
@zai varchar(10)
)
AS
begin
declare @re table(NodeName varchar(10),grade int)
declare @grade int
set @grade=0
insert into @re select @box_d,@grade
while @@rowcount>0
begin
set @grade=@grade+1
insert into @re select a.box_x,@grade from l_box_box as a,@re as b where a.box_d=b.NodeName and b.grade=@grade-1
end
end--丢了个end
----创建测试数据
declare @ta table(pid varchar(10),id varchar(10))
insert @ta
select 'd','z' union all
select 'z','x' union all
select 'z','y' union all
select 'x','a' union all
select 'y','b'
----查找包含的数据
declare @tb table(A varchar(10),B varchar(10)) /*结果表*/
declare @pid varchar(10)
declare @str varchar(10)
set @pid = 'd' /*请楼主分别设置z,x,y试试,看看结果是否符合要求*/
set @str = 'h'
insert into @tb select id,@str from @ta where pid = @pid
while @@rowcount > 0
begin
insert into @tb select a.id,@str from @ta a inner join @tb b on a.pid = b.A
where a.id not in(select A from @tb)
end
----查看结果
select * from @tb
drop table #test
if object_id('tempdb..#test2') is not null
drop table #test2
GO
----创建测试数据
create table #test(id int identity(1,1),name varchar(10),dt datetime)
insert #test(name,dt)
select 'a',getdate() union all
select 'b',getdate() union all
select 'c',getdate() union all
select 'd',getdate() union all
select 'e',getdate()
create table #test2(id int identity(1,1),name varchar(10),dt datetime)----声明从游标读出的变量
declare @id int,@name varchar(10),@dt datetime
----声明游标
declare cur CURSOR for select id,name,dt from #test
----打开游标
open cur
----提取游标第一行,将当前游标行的列值输出到指定变量中
fetch next from cur into @id,@name,@dt
----如果游标提取成功则循环提取游标的行,直到游标最后一行
while @@fetch_status = 0
begin
--在循环中写处理代码.本例中修改当前行的name列值
update #test set name = name + '-' + name where current of cur
--在循环中写处理代码.本例中将每行的列值插入到相同结构的test2表中
insert #test2(name,dt) select @name,@dt
--当前行处理结束后向下移动一行游标
fetch next from cur into @id,@name,@dt
end
----游标遍历结束后关闭游标
close cur
----释放游标占用的内存
deallocate cur
----查询对test表的修改
select 'test',* from #test
----查询游标遍历处理的结果
select 'test2',* from #test2----清除测试环境
drop table #test,#test2更多信息,请参考DECLARE CURSOR帮助.还有一种游标定义方法,请参考CREATE PROCEDURE帮助中例子E.