游标遍历exec动态sql语句查询结果

游标比较特殊,可以在EXEC()中创建后再引用,通常用于动态游标.可以这样处理:

declare @sql varchar(8000),@table sysname
set @table = 'table1'
set @sql='DECLARE abc CURSOR FOR select * from ' + @table
exec(@sql)
OPEN abc
...

下面是动态游标测试的例子:

if object_id('tbTest') is not null
drop table tbTest
GO
----创建测试数据
create table tbTest(id int,name varchar(10))
insert tbTest
select 1,'a' union all
select 2,'b' union all
select 3,'c' 
GO
declare @table sysname,@id int,@name varchar(10)
declare @sql varchar(1000)
set @table = 'tbTest'
----定义动态游标
set @sql = 'declare abc CURSOR for select id,name from ' + @table
----创建动态游标
EXEC(@sql)
----打开游标
OPEN abc
FETCH NEXT FROM abc INTO @id,@name
WHILE @@fetch_status = 0
begin
    select @id,@name
    FETCH NEXT FROM abc INTO @id,@name
end
----清除游标
CLOSE abc
DEALLOCATE abc
----清除测试环境
drop table tbTest

/*结果
----------- ----------
1 a


----------- ----------
2 b


----------- ----------
3 c
*/