i want to create a procedure in informix in which table name is in a variable (strTable) and i want to retrieve the records from that table. Later i will link this procedure to crystal report to create a report of the records.

eg.
Define strTable Varchar(30);
Let strTable = "Employee";
Select * from strTable;

I tried dynamic sql statements "prepare" and "execute" in the procedure but it didn't work.
eg.
CREATE PROCEDURE tempProc()
RETURNING
VarChar(30);
DEFINE tmpstr VARCHAR(255);
Define rgn varchar(255);
Let tmpstr='Employee';

foreach curTemp For
prepare s1 from "Select Region_name from ?"
execute s1 using tmpstr
WITH RESUME;
END FOREACH
END PROCEDURE;

pls help me if u know any solution to this problem.
Regards,
Puneet
(puneetjaggi@indiatimes.com)

Hi,

Informix Stored procedure language does not support Dynamic SQL.

You have to write a C-program and use a SQLDA or Descriptor area.

You can also use shell scripting and use environment variable in all inline sql statements.

Eg

MYTABLE="sysusers"
dbaccess -e somedb <<EOF

unload to /tmp/$MYTABLE.unl
select * from $MYTABLE;

EOF