Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Question Unanswered: Set value for variable in a declared cursor

    Hi,

    I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

    ------------------------------------------------------------------------
    DECLARE @str_var VARCHAR(10)
    DECLARE @field_val VARCHAR(10)

    DECLARE c1 CURSOR LOCAL FOR
    SELECT field1 FROM tableA WHERE field1 = @str_var


    WHILE (Sometime TRUE)
    BEGIN

    ....

    SET @str_var = 'set to some values, eg. ABC123, XYZ123'

    OPEN c1

    FETCH c1 INTO @field_val

    WHILE (@@fetch_status != -1)
    BEGIN

    PRINT @field_val
    ...

    FETCH c1 INTO @field_val
    END

    CLOSE c1

    END

    DEALLOCATE c1

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

    Thanks a lots,
    Vincent

  2. #2
    Join Date
    Feb 2004
    Posts
    6
    I think this approach is impossible because when you dclare a cursor is will be cached and evaluted using current value of your parameters(str_var) and further changes to (str_var) does not affect curosr.

  3. #3
    Join Date
    Sep 2003
    Posts
    13
    Originally posted by Protege
    I think this approach is impossible because when you dclare a cursor is will be cached and evaluted using current value of your parameters(str_var) and further changes to (str_var) does not affect curosr.

    Thank you for reply.

    I am migrating some stored procedures from Sybase to SQL Server 2000. The example is copied from Sybase stored proc. It is work in Sybase platform. However, the behavior is different in SQL 2000. The @str_var cannot be further change after declare the cursor. Does anyone know any alternative to work around?

    Thanks a lot.

  4. #4
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    Can you declare the cursor inside the first while, and deallocating it before its end?
    Last edited by jigarzon; 02-16-04 at 11:10.

  5. #5
    Join Date
    Sep 2003
    Posts
    13
    An error message said that the cursor already defined. But I think it is possible to declare the cursor inside the first WHILE and deallocate it once the cursor is closed. Is it possible to declare the cursor just like a prepared statement and the parameters can be specified after the cursor declared? Otherwise, the declare statement will be called many times in the WHILE loop.

Posting Permissions

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