Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Unanswered: Parameter Marker in Dyanmic SQL??

    Hi,

    I have a Static SQL and wish to write as a Dynamic SQL since the FROM Clause and WHERE Clause may change depending on some situations

    Here is the Static SQL:

    SELECT COUNT(*)
    INTO :WS-COUNT
    FROM EC181
    WHERE PRCS_STTS_NM = 'NEW'

    I understand that Host Variables cannot be used in Dynamic SQL and that Parameter Marker may be used in its place.
    However, I have not seen any examples of how a Paramerter Marker can be used. Also, if I use Parameter Marker in place of WS-COUNT, how can I capture the count value in any variable? The count value captured needs to be passed to another program.

    Please let me know how to execute the above SQL as a Dynamic SQL.

    Thanks!!

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    Host Variables cannot be used in Dynamic SQL ????
    the statement is a character string/variable
    if a variable (with predicate where value) is placed in this string, this will be taken in account when prep is executed...
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    You cannot use the SELECT ... INTO ... with dynamic SQL, you must use a cursor instead.

    An Example for PL/1:

    DCL HOST CHAR(54);
    HOST = 'SELECT COUNT(*) FROM EC181 WHERE PRCS_STTS_NM = ''NEW'' ';

    EXEC SQL DECLARE CUR1 FOR STMT;
    EXEC SQL PREPARE STMT FROM :HOST ;
    EXEC SQL OPEN CUR1 ;
    EXEC SQL FETCH CUR1 INTO :WS_COUNT ;

    Parameter marker may be used in the WHERE-clause:
    DCL HOST CHAR(54);
    DCL PARM1 CHAR(3);
    EXEC SQL DECLARE CUR1 FOR STMT;
    HOST = 'SELECT COUNT(*) FROM EC181 WHERE PRCS_STTS_NM = ? ';
    PARM1 = 'NEW' ;
    EXEC SQL PREPARE STMT FROM :HOST;
    EXEC SQL OPEN CUR1 USING :PARM1 ;
    EXEC SQL FETCH CUR1 INTO :WS_COUNT ;

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Parameter markers (i.e. ?) can be used with dynamic SQL. Maybe you are talking about host variables here?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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