Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Dynamic Cursor/ Dynamic SQL Statement

    I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

    Using the examples given in Books Online returns compilation errors. See below.

    Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

    James



    -- SQL ---------------

    EXEC SQL BEGIN DECLARE SECTION;
    char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
    char szLastName[] = "White";
    char szFirstName[30];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL
    DECLARE author_cursor CURSOR FOR select_statement;

    EXEC SQL
    PREPARE select_statement FROM :szCommand;

    EXEC SQL OPEN author_cursor USING :szLastName;
    EXEC SQL FETCH author_cursor INTO :szFirstName;



    --Error--------------------
    Server: Msg 170, Level 15, State 1, Line 23
    Line 23: Incorrect syntax near ';'.
    Server: Msg 1038, Level 15, State 1, Line 24
    Cannot use empty object or column names. Use a single space if necessary.
    Server: Msg 1038, Level 15, State 1, Line 25
    Cannot use empty object or column names. Use a single space if necessary.
    Server: Msg 170, Level 15, State 1, Line 27
    Line 27: Incorrect syntax near ';'.
    Server: Msg 170, Level 15, State 1, Line 30
    Line 30: Incorrect syntax near 'select_statement'.
    Server: Msg 170, Level 15, State 1, Line 33
    Line 33: Incorrect syntax near 'select_statement'.
    Server: Msg 102, Level 15, State 1, Line 35
    Incorrect syntax near 'author_cursor'.
    Server: Msg 170, Level 15, State 1, Line 36
    Line 36: Incorrect syntax near ':'.

  2. #2
    Join Date
    Aug 2003
    Posts
    111
    using the following seem to be achieving what i wanted.
    but i would still like to know how to use
    1. PREPARE
    2. EXECUTE
    i.e. under what circumstances would you use those 2?
    It must be there for a reason.

    James


    --Working SQL---------------

    DECLARE @sql nvarchar(4000)
    SET @sql = 'DECLARE @name nvarchar(128) ' +
    'DECLARE test_cursor CURSOR FOR SElECT name FROM class_category ' +
    'OPEN test_cursor ' +
    'FETCH NEXT FROM test_cursor INTO @name ' +
    'BEGIN ' +
    'PRINT @name ' +
    'FETCH NEXT FROM test_cursor INTO @name ' +
    'END '
    EXECUTE sp_executesql @sql

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You seem to be confusing the VB environment with the Transact-SQL environment. The VB code executes only on the client side, and that is where a PREPARE might make sense. The Transact-SQL code executes only on the server side, and you can't explicitly prepare code there (at least under normal circumstances).

    -PatP

Posting Permissions

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