Re: Using dynamic SQL in Informix's stored procedure..
This is an Informix queston, but I'll try to reply anyway. You'd be better off with MySQL though :-)
To begin with, Informix SPL does not support dynamic SQL (i.e. you cannot use the PREPARE statement).
Secondly, the way you have written your code, the table name is a parameter, which is not allowed syntax. You cannot have a parameter as a table name, column name or a syntactic element, only as a value, typically in a WHERE clause.
Thirdly, the control flow in your procedure is incorrect (but this matters little, as it wouldn't work anyway because of the two problems noted above. In general, the FOREACH loop is used to loop through the rows of a declared cursor. So if we assume that the PREPARE statement really WAS supported, which it is not, the your code should look somethng like this (appologize for any small errors here, it was some years ago since I wrote Informix SPL code):
Let tmpstr='Employee';
prepare s1 from "Select Region_name from ?";
foreach curTemp For s1
FETCH s1 INTO somecol;
RETURN somecol WITH RESUME;
END FOREACH
END PROCEDURE;
(I admit that the above has several problems. For example that the procedure can only return 1 column. But the # of columns has to be known at the time the SPL routine is declared in Informix SPL).
Now, one possible way to deal with this would be to return a ROW type, and using table inheritance or something like that. I'm not SURE that would work though, probably not, this is just a thought. And this would only fix the problem of being able to return variable # of columns. The dynamic nature of thing MIGHT be solved with an external routine in a REAL programming language (like C) :-)
Good luck