Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    29

    Unanswered: Cursor / While Loop question

    Is this possible? (I cannot get it to work).
    I want to use a variable (@emp_id) to loop through a cursor and call a procedure each time, with the new @emp_id as part of the CURSOR.
    The variable @emp_id is part of the CURSOR

    -------------------

    DECLARE @emp_id INTEGER
    SELECT @emp_id = 708
    GO

    WHILE (@emp_id <= 720)
    BEGIN

    DECLARE cons_cursor_1 CURSOR FOR
    SELECT a.employee_id, a.points, a.defensive_rebounds
    FROM player_stats_history a, employee b, game c
    WHERE a.game_id like '00203%' AND
    a.employee_id = b.employee_id AND
    a.game_id = c.game_id AND
    a.employee_id = @emp_id
    GO

    OPEN cons_cursor_1
    EXEC procedure_consistency
    CLOSE cons_cursor_1
    DEALLOCATE CURSOR cons_cursor_1
    GO

    SELECT @emp_id = @emp_id + 1
    END

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    Can you pls. elaborate what you are trying to acheive here, as I see you are not even looping through cursor. Also, what the SP is trying to do with Cursor result. Based on what you have right now, I will use temp tables to get the data and run SP on it.

  3. #3
    Join Date
    Apr 2004
    Posts
    29
    The procedure uses the cursor result to perform mathematical computations on each emp_id.

    I want the while loop to loop through emp_ids. Variable @emp_id will change for each employee and will be part of the cursor. Much more effective to have the emp_id as part of the select than to check with an IF condition.

    Thanks.

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    Can you pls. post the error message with Sybase version?

  5. #5
    Join Date
    Apr 2004
    Posts
    29
    Server 'ds_NBA_Dev_A01', Line 2:
    The cursor 'cons_cursor_1' can not be used as it could not be found. It is possible that either it was not declared or it is not available in the current context.
    (return status = -6)
    Server Message: Number 137, Severity 15
    Server 'ds_NBA_Dev_A01', Line 1:
    Must declare variable '@emp_id'.

    ----

    Sybase SQL Advantage 11.5.1.3

  6. #6
    Join Date
    Nov 2002
    Posts
    207
    Only solutions would be...

    create proc foo
    as
    DECLARE @emp_id INTEGER
    SELECT @emp_id = 708

    WHILE (@emp_id <= 720)
    BEGIN

    DECLARE cons_cursor_1 CURSOR FOR
    SELECT a.employee_id, a.points, a.defensive_rebounds
    FROM player_stats_history a, employee b, game c
    WHERE a.game_id like '00203%' AND
    a.employee_id = b.employee_id AND
    a.game_id = c.game_id AND
    a.employee_id = @emp_id

    OPEN cons_cursor_1
    EXEC procedure_consistency
    CLOSE cons_cursor_1
    DEALLOCATE CURSOR cons_cursor_1

    SELECT @emp_id = @emp_id + 1
    END

    I tested it and it works. Instead of calling the SQL, you just call the SP foo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •