sqlserver2000动态游标的问题

0 1

sqlserver2000动态游标的问题0

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

这段代码

   declare cur_votedept cursor  for select  top 10 * from sp_vote_dept_records

2005编译通过，但是2000中报错。

之后看网上的例子这样改了：

--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);

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

sqlserver 动态游标

2012年9月10日 14:21

lohasle
-1
0 1 6

采纳的答案

--使用游标遍历临时表#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

2012年9月10日 17:22

eleven011
25
0 0 5

添加评论

0 0

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
其中
LOCAL
指定对于在其中创建的批处理、存储过程或触发器来说，该游标的作用域是局部的

2012年9月10日 14:41

demojava
2207
1 3 134

添加评论

0 0

SET NOCOUNT ON 
  
DECLARE @vendor_id int, @vendor_name nvarchar(50), 
@message varchar(80), @product nvarchar(50) 
  
PRINT -------- Vendor Products Report -------- 
  
DECLARE vendor_cursor CURSOR FOR  
SELECT VendorID, Name 
FROM Purchasing.Vendor 
WHERE PreferredVendorStatus = 1 
ORDER BY VendorID 
  
OPEN vendor_cursor 
  
FETCH NEXT FROM vendor_cursor  
INTO @vendor_id, @vendor_name 
  
WHILE @@FETCH_STATUS = 0 
BEGIN 
PRINT 
SELECT @message = ----- Products From Vendor: + 
@vendor_name 
  
PRINT @message 
  
-- Declare an inner cursor based   
-- on vendor_id from the outer cursor. 
  
DECLARE product_cursor CURSOR FOR  
SELECT v.Name 
FROM Purchasing.ProductVendor pv, Production.Product v 
WHERE pv.ProductID = v.ProductID AND 
pv.VendorID = @vendor_id-- Variable value from the outer cursor 
  
OPEN product_cursor 
FETCH NEXT FROM product_cursor INTO @product 
  
IF @@FETCH_STATUS <> 0  
PRINT          <<None>>     
  
WHILE @@FETCH_STATUS = 0 
BEGIN 
  
SELECT @message =          + @product 
PRINT @message 
FETCH NEXT FROM product_cursor INTO @product 
  
END 
  
CLOSE product_cursor 
DEALLOCATE product_cursor 
  
-- Get the next vendor. 
FETCH NEXT FROM vendor_cursor  
INTO @vendor_id, @vendor_name 
END  
CLOSE vendor_cursor 
DEALLOCATE vendor_cursor

2012年9月10日 14:27

demojava
2207
1 3 134

1条评论

已解决问题

未解决问题

排行榜

查看全部排名>>