RE: Dynamic SQL

["Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>]

<snip>
Since there are only four possible values for my file name, I have
resorted to a select:

Select:
         When p_File = 'MKCUSW';
           Exec SQL Select count(*) into :w_Count From MKCUSW;
         When p_File = 'MKPRODW';
           Exec SQL Select count(*) into :w_Count From MKPRODW;
         When p_File = 'MKOHLW';
           Exec SQL Select count(*) into :w_Count From MKOHLW;
         When p_File = 'MKPROMOW';
           Exec SQL Select count(*) into :w_Count From MKPROMOW;
EndSL;      
</snip>
Depends on what you are trying to do. You cannot use parameter markers
for a file name but you can simply build the sql statement and prepare. 

SqlStatement = 'Select Count(*) From ' + FileName;
Exec Sql Prepare S1 From SqlStatement;


Parameter markers are used for things that will change each time that a
SQL statement is opened. What this applies to is where clauses. 

Where Customer# = ? or
Where Date between ? and ? etc

The SQL statement is prepared once and then you just give it different
values each time that you open. 

Yes, you cannot use "Into" in a prepared statement. It is used for
different things. This from the manual.

------------------------------------------------------------------------
---
If INTO is used, and the PREPARE statement is successfully executed,
information about the prepared statement is placed in the SQLDA
specified by the descriptor-name. 

Thus, the PREPARE statement: 
EXEC SQL PREPARE S1 INTO :SQLDA FROM :V1;

is equivalent to: 

EXEC SQL PREPARE S1 FROM :V1;
EXEC SQL DESCRIBE S1 INTO :SQLDA; descriptor-name
------------------------------------------------------------------------
-

Instead, use a Set

Exec Sql Set :w_count = (Select count(*) From MKPROMOW);

You might want to take a look at the SQL Manual under Prepare and Set. 

I am not sure if this will work.

SqlStatement = 'Select Count(*) From ' + FileName;
Exec Sql Prepare S1 From SqlStatement;
Exec Sql Set :w_count = (Execute S1);