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!!

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...

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 ;

Parameter markers (i.e. ?) can be used with dynamic SQL. Maybe you are talking about host variables here?