SQL Server Performance

Simple Cursor for SP

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by tboonleong, Jun 4, 2007.

  1. tboonleong Member

    Hi,
    I am new to tart writing Store prod with cursor.
    I encounter the following simple issue.
    Hope able to get help from you all.
    Thanks.

    Code for SP:
    =======================

    Declare @SQLStatement AS varchar(4000)
    Declare @A AS varchar(255)

    SET @SQLStatement = 'SELECT a FROM tableA '

    DECLARE mv_cursor CURSOR for @SQLStatement;
    OPEN mv_cursor

    FETCH NEXT FROM mv_cursor
    INTO @A
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM mv_cursor
    INTO @A
    END
    CLOSE mv_cursor
    DEALLOCATE mv_cursor



    The above code hit error on the this statement:
    DECLARE mv_cursor CURSOR for @SQLStatement

    Error msg : Incorrect syntax near '@SQLStatement'.

    If i change it to DECLARE mv_cursor CURSOR for SELECT a FROM tableA
    then it works.
    Anyone know why DECLARE mv_cursor CURSOR for @SQLStatement; does not work?


  2. FrankKalis Moderator

  3. Madhivanan Moderator

    Anyway, why do you need dynamic sql+cursor?
    Can you expain what you are trying to do?

    Madhivanan

    Failing to plan is Planning to fail
  4. shashikant New Member

    Hi dear
    your problem is that u r not running your table variable query
    so try this code


    Declare @SQLStatement AS varchar(4000)
    Declare @A AS varchar(255)

    SET @SQLStatement = 'SELECT a FROM tableA '

    DECLARE mv_cursor CURSOR for
    exec(@SQLStatement);
    OPEN mv_cursor

    FETCH NEXT FROM mv_cursor
    INTO @A
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM mv_cursor
    INTO @A
    END
    CLOSE mv_cursor
    DEALLOCATE mv_cursor



    regards
    shashi kant

    Keep In Touch
    Thanks & Regards
    Shashi Kant Chauhan
  5. tboonleong Member

    thanks for you help.<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page