求循环的SQL语句。

[zl194]

在SQL2000中有这样一个表

SELECT 'AA' AS C1,1 AS C2,21 AS C3 INTO mytb1 UNION
SELECT 'AA' AS C1,2 AS C2,43 AS C3 UNION
SELECT 'AA' AS C1,3 AS C2,12 AS C3 UNION
SELECT 'AA' AS C1,4 AS C2,23 AS C3 UNION
SELECT 'AA' AS C1,5 AS C2,32 AS C3 UNION
SELECT 'AA' AS C1,6 AS C2,24 AS C3 


SELECT  * from mytb1

我想用表里的C1,C2,C3 作为参数 执行另一个存储过程，这样的循环该怎么写呢。

[百年树人]

用游标吧

[chuifengde]

游标。。

[nightmaple]

declare cursor_A cursor
for SELECT * from mytb1
declare @C1 varchar(20),@C2 varchar(20),@C3 varchar(20)
open cursor_A
fetch next from cursor_A into @C1,@C2,@C3
while (@@fetch_status=0)
begin
	exec procname @C1,@C2,@C3
	fetch next from cursor_A into @C1,@C2,@C3
end
close cursor_A
deallocate cursor_A

[nightmaple]

如果你是要多次执行结果放到一个表中，可以用下面的

--同执行存储过程返回的结构一样的表
create table #result
(
	--字段列表
)
go

declare cursor_A cursor
for SELECT * from mytb1
declare @C1 varchar(20),@C2 varchar(20),@C3 varchar(20)
open cursor_A
fetch next from cursor_A into @C1,@C2,@C3
while (@@fetch_status=0)
begin
	insert into #result exec procname @C1,@C2,@C3
	fetch next from cursor_A into @C1,@C2,@C3
end
close cursor_A
deallocate cursor_A

select * from #result

[永生天地]

能不能再具体的说一下呢

[ask_chang]

declare cursor_A cursor
for SELECT * from mytb1
declare @C1 varchar(20),@C2 varchar(20),@C3 varchar(20)
open cursor_A
fetch next from cursor_A into @C1,@C2,@C3
while (@@fetch_status=0)
begin
exec procname @C1,@C2,@C3
fetch next from cursor_A into @C1,@C2,@C3
end
close cursor_A
deallocate cursor_A

支持#3

[zl194]

谢谢大家。

[ask_chang]

如果是函数的话
select dbo.函数名称（c1,c2,c3） from mytb1

个人见解

[TheGodOfGods]

没用过游标，帮顶