Post reply

How do I use dynamic SQL in Cursor declaration

  • May 18, 2006 at 8:41 am

    #113030

    Hi folks,

    I need to create a cursor by:

    Select WorkRequestID From WorkRequest

    there should be a where clause in the SQL select statement. The clause will be pulled out from a table:

     select @clause = Clause From CursorClause

     Where @joblevel = Joblevel and @StatusID = StatusID

     

     Now, I want to do this in cursor:

     Declare c Cursor for

     Exec ('Select WorkRequestID From WorkRequest w ' + @clause)

    However, it seems SQL doesn't recognize it, what's the problem? How can I create the cursor?

    Thanks.

  • May 18, 2006 at 9:11 am

    #638772

    Maybe you could find some way how to do it without cursor and dynamic SQL?

    Since I try to avoid both cursors and dynamic SQL, I'm not absolutely sure about it, but I think you can't use both at the same time. It could be possible to create a temporary table, fill it using EXEC and then declare the cursor from temporary table... but I strongly suggest to look for some other solution, not involving cursors at least.

  • May 18, 2006 at 9:31 am

    #638777

    Agree with Vladan

    But if you must do this using a cursor then

    DECLARE @sql nvarchar(4000)

    SET @sql = 'DECLARE c CURSOR FOR SELECT WorkRequestID FROM WorkRequest w ' + @clause

    EXEC(@sql)

    OPEN c

    ...

    CLOSE c

    DEALLOCATE c

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply