Re: [Q] Pro*C/Dynamic SQL, can the WHERE clause be dynamic?

In article <dedgar.58.0014FD66_at_fearless.csi.nb.ca>, dedgar_at_fearless.csi.nb.ca (Dale Edgar) writes:
|> Hi All
|>
|> Can the WHERE clause be dynamic in Pro*C (method 3 or 4?).
|>
|> What I'd like to do is be able to code up a routine able to process runtime
|> created WHERE statements like:
|>
|> SELECT :empno, :empname FROM emp WHERE empname = :stringvar;
|>
|> and something like
|>
|> SELECT :empno, :empname FROM emp WHERE SOUNDEX(empname) = SOUNDEX(:stringvar);
|>
|> The select-list items are always the same, its the construction of the WHERE
|> part of the statement that will vary according to the users inputs.
|>
|> Is this do-able or does one hard code a bunch of regular Pro*C SQL querys
|> for the job.
|>
|> Many Thanks in Advance
|> Dale Edgar
|> Cybersmith Inc.
|> dedgar_at_csi.nb.ca
|>
|>

Yep. You can't use a bind variable for this, however. Just create the string from which you prepare dynamicly. Like:

strcpy(sqlstatement, "SELECT :empno, :empname FROM emp WHERE empname = "); strcat(sqlstatement, stringvar);

EXEC SQL PREPARE S from :sqlstatement;

... Received on Fri Jan 20 1995 - 02:45:51 CET