sqlserver2000动态游标的问题

Question

怎么解决sqlserver2000 中动态游标 top 后面跟变量？？
declare cur_votedept cursor for select top 10 * from sp_vote_dept_records 这样的 05是不会报错的
但是到2000中就报错了

这段代码
[code="sql"]
declare cur_votedept cursor for select top 10 * from sp_vote_dept_records
[/code]
2005编译通过，但是2000中报错。

之后看网上的例子这样改了：
[code="sql"]
--declare @sqlExec varchar(6000);

-- set @sqlExec='declare cur_votedept cursor for
-- select top ('+convert(varchar,@topnum)+') * FROM sp_vote_dept_records
-- where deptId='+@deptId+' and voteId='+@voteIdOut+' order by ticketNum desc';

-- exec(@sqlExec);
[/code]

但还是没用报错，求教。有MSＳＱＬ２０００动态游标的例子那就好了。

Answer 1

--使用游标遍历临时表#t
declare pcurr cursor for select sn,name,minc,maxc from #t

open pcurr

declare @psn varchar(20)
declare @pname varchar(20)
declare @pminc float
declare @pmaxc float
fetch next from pcurr into @psn,@pname,@pminc,@pmaxc
while (@@fetch_status = 0)

begin
--每次创建一个临时表#t1,用后删除
CREATE TABLE #t1(
sn varchar(20),
name varchar(20),
credit float);
insert #t1
select sn,name,credit from tbl_test_course where sn=@psn and name=@pname ;
--遍历#t1
declare pcurr1 cursor for select credit from #t1
open pcurr1

declare @credit float
fetch next from pcurr1 into @credit
while (@@fetch_status = 0)

begin
if @credit>@pmaxc
set @pmaxc=@credit
if @credit<@pminc
set @pminc=@credit
fetch next from pcurr1 into @credit
end

--循环之后@pminc存储的最低分数，@pmaxc存储的最高分数将临时表的记录最修改即可
update #t set minc=@pminc,maxc=@pmaxc where sn=@psn and name=@pname;
close pcurr1

deallocate pcurr1
drop table #t1;
fetch next from pcurr into @psn,@pname,@pminc,@pmaxc
end

close pcurr

deallocate pcurr
select * from #t
END