Re: Embedded SQL and Parameter Markers

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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.