zfu3382548
2012-09-10 14:21
浏览 294
已采纳

sqlserver2000动态游标的问题

怎么解决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]

但还是没用报错,求教。有MSSQL2000动态游标的例子那就好了。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • 地球小星星 2012-09-10 17:22
    已采纳

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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • iteye_5246 2012-09-10 14:27

    [code="SQL"]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 <>

    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

    [/code]

    评论
    解决 无用
    打赏 举报
  • iteye_5246 2012-09-10 14:41

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

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题