×

Loading...

Topic

This topic has been archived. It cannot be replied.
  • 工作学习 / IT技术讨论 / Is there anybody who has the experience with SQL Server's Stored Procedure and cousor? Please come in. -cyt(会飞的鹿); 2001-11-16 {851} (#263571@0)
    I'm wondering how come I'm not able to fecth the column in the cursor Cur_repnum into the variable @repNum.
    Platform is SQL Server 7.0

    create PROCEDURE test
    @empId VARCHAR
    AS
    DECLARE @repNum INTEGER

    SET @repNum = 0
    DECLARE Cur_repnum CURSOR FOR SELECT report_number FROM personal_exp_report_table
    WHERE employee_id = @empId order by report_number

    OPEN Cur_repnum
    FETCH NEXT FROM Cur_repnum INTO @repNum
    select 'REPNUM= ' + CONVERT(varchar(9), @repNum)
    ----WHILE @@FETCH_STATUS=0...
    CLOSE Cur_repnum
    DEALLOCATE Cur_repnum
    GO


    exec test 'RD11'

    -----------------
    REPNUM= 0

    (1 row(s) affected)


    SELECT report_number FROM personal_exp_report_table
    WHERE employee_id = 'RD11' order by report_number

    report_number
    -------------
    8
    9
    10
    11
    12
    14
    15
    16

    (8 row(s) affected)
    • 你的ORDER BY 有问题，不能ORDER BY 所FETCH的COLUMN -bigrice(大米); 2001-11-16 (#263632@0)
    • I got it -cyt(会飞的鹿); 2001-11-16 {662} (#263688@0)
      When I try to simplify the question :

      DECLARE @empId VARCHAR

      SET @empId ='RD11'

      SELECT report_number FROM navsys.personal_exp_report_table
      WHERE employee_id = 'RD11' order by report_number

      select @empID

      SELECT report_number FROM navsys.personal_exp_report_table
      WHERE employee_id = @empId order by report_number

      I got :

      report_number
      -------------
      8
      9
      10
      11
      12
      13
      14
      15
      16

      (9 row(s) affected)


      ----
      R

      (1 row(s) affected)

      report_number
      -------------

      (0 row(s) affected)

      So What I need to do is to re-declare
      DECLARE @empId VARCHAR(30)

      Thanks for all your attention anyway.
枫下论坛主坛 /  工作学习 /  IT技术讨论