×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Las Vegas

Is there anybody who has the experience with SQL Server's Stored Procedure and cousor? Please come in.

cyt(会飞的鹿)
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)
(#263571@0)
Last Updated: 2001-11-16
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 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.

More Topics

  • 脑洞大开，突发奇想，用力来传播信息
  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论