Re: Embedded SQL and Parameter Markers

Craig

Take a look at the extended indicators support. This is an extension of the null indicator use. This is used in INSERT and UPDATE statements, including in the field list, I think I just read, of the SELECT of an INSERT. A -7 value of one of these indicators means "unassigned", so in some scenarios, it is not used.

I know this exists, I don't know if it fits what you want to do.

HTH
Vern

On 2/17/2022 3:14 AM, Craig Richards wrote:

I'm building a few "Work With" screens with filters at the moment
Fairly typical maintenance programs where several of the columns shown in a
subfile have a "filter" field above them for refining the record selection.

Because the number of filters used is variable, I have traditionally
created dynamic SQL by building up a WHERE clause in a string and adding in
any of the filter fields which have been entered on the screen by the user.

I was wondering if there is a reasonable way to do this using parameter
markers.
Given that the number of filters entered is variable, so would the list of
parameter markers be.

To illustrate - I'm doing something like (simplified for brevity):

SqlStm = 'Select ... From ...';

If Filter1 <> *Blanks;
SqlWhere += 'and Field1 = Filter1'
EndIf;

If Filter2 <> *Blanks;
SqlWhere += 'and Field2 = Filter2'
EndIf;

If SqlWhere <> *Blanks;
SqlStm += SqlWhere;
EndIf;

etc.

In this simple example I could end up with:
- Select * from File
- Select *from File where Field1 = Value1
- Select *from File where Field2 = Value2
- Select *from File where Field1 = Value1 and Field2 = Value2

Is there a better way to build this dynamic sql?

Thanks in advance for any suggestions.