存储过程中如何使用动态的游标

[goln_cn]

声明游标：declare blur_cur scroll cursor for select name from testtbl

但因为我的SELECT语句是动态生成的，而这样定义：
declare @nvar_sql nvarchar(2000)
set @nvar_sql='select name from testtbl'
declare blur_cur scroll cursor for exec sp_executesql @nvar_sql
语法不允许。
当然，我可以写另外一个存储过程用来返回游标，然后在此存储过程中使用；但如果要在一个存储过程里处理，有什么办法呢

[txlicenhe]

declare @nvar_sql nvarchar(2000)
create table ##tmp (name varchar(10))
set @nvar_sql='select name into ##tmp from testtbl'
exec(@nvar_sql)
declare blur_cur scroll cursor for Select * from ##tmp

...
drop table ##tmp

[teaism]

declare @sqlstr nvarchar(1000)
set @sqlstr='declare a cursor for select * from t'
exec(@sqlstr)
open a
fetch next from a

[txlicenhe]

create procedure test1 ..
as
..
set @nvar_sql='select name from testtbl'
Exec(@nvar_sql)
go

然后
create table #tmp (name varchar(10))
insert #tmp exec test1
declare blur_cur scroll cursor for Select * from #tmp

...
drop table #tmp

[teaism]

declare @nvar_sql nvarchar(2000)
set @nvar_sql='declare a cursor for select name from testtbl'
exec(nvar_sql)
open a
fetch next from a

[teaism]

游标可以直接使用。

[pengdali]

declare @nvar_sql nvarchar(2000)
set @nvar_sql='declare blur_cur cursor for select name from testtbl'

open blur_cur