Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Dynamic SQL in cursor

    I need to pass a list of values into a cursor as such...


    DECLARE
    @group_SQL varchar(255)

    SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')'

    DECLARE groupContact_import_cursor CURSOR
    FOR EXEC(@group_SQL)
    OPEN groupContact_import_cursor
    FETCH NEXT FROM groupContact_import_cursor INTO @group_id
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    insert into groupContacts (group_id, contact_id) values (@group_id, @new_cid)
    FETCH NEXT FROM groupContact_import_cursor INTO @group_id
    END
    CLOSE groupContact_import_cursor
    DEALLOCATE groupContact_import_cursor

    But MS SQL doesn't seem to like the FOR EXEC(@group_SQL). Can someone shed some light?

    TIA

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    I think it should work like this.

    DECLARE groupContact_import_cursor CURSOR
    FOR SELECT group_id FROM groups where group_id = @group_id

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    Why? A contact can be assigned to many group_id values, not just one.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')'

    set @group_SQL = 'DECLARE groupContact_import_cursor CURSOR FOR ' + @group_SQL

    exec (@group_SQL)
    OPEN groupContact_import_cursor
    ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Mar 2003
    Posts
    97
    bingo, that did it. I didn't know I needed to include the DECLARE portion, oh well...

    Thanks a lot.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by naceBal
    bingo, that did it. I didn't know I needed to include the DECLARE portion, oh well...

    Thanks a lot.
    It did?

    I'm highly sceptical....


    What does @group_id look like
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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