Hello Timothy.

Thanks for your article.

Your better (variadic arguments) solution can be improved. It has the following disadvantages:

1. You have to repeat many times

   set args($increment(args)) =

2. You have to add a space at the end of every part (except of the last one). For example:

   "and Product = ? "

 As an inspiration you can look at this class. We use it in our product.

Class L3.SQL.QueryBuilder Extends %RegisteredObject
{

Property STATEMENT [ MultiDimensional, Private ];

Property PARAMS [ MultiDimensional, Private ];

Method Add(line As %String, params... As %String)
{
    If $Get(line)="" Set $Ecode="Parameter 'line' is empty."

    Set i=$Increment(..STATEMENT)
    Set ..STATEMENT(i)=line
    If '$Data(params) Return

    For j=1:1:params {
        Set k=$Increment(..PARAMS)
        Set ..PARAMS(k)=params(j)
    }
}

Method Execute(Output statementResult As %SQL.StatementResult) As %Status
{
    #dim statement As %SQL.Statement
    #dim sc As %Status

    Kill statementResult

    If +$Get(..STATEMENT)=0 Set $Ecode="Empty statement."

    ; Set SelectMode to 0. 
    Set statement=##class(%SQL.Statement).%New(0)
    Merge STATEMENT=..STATEMENT
    Set sc=statement.%Prepare(.STATEMENT)
    If $$$ISERR(sc) Return sc

    Merge PARAMS=..PARAMS
    Set statementResult=statement.%Execute(PARAMS...)
    If (statementResult.%SQLCODE'=0) && (statementResult.%SQLCODE'=100) {
        Return $System.Error.FromSQLCode(statementResult.%SQLCODE,statementResult.%Message).Status
    }

    Return $$$OK
}

}

Now I am able to rewrite you example code like this:

set qb=##class(L3.SQL.QueryBuilder).%New()
do qb.Add("select Product->Name, Outlet->City, AmountOfSale, UnitsSold")
do qb.Add("from HoleFoods.SalesTransaction where Actual = 1")

if (product '= "") {
    do qb.Add("and Product = ?", product)
}
if (channel '= "") {
    do qb.Add("and Channel %INLIST ?", channel)
}
if (minProductPrice '= "") {
    do qb.Add("and Product->Price >= ?", minProductPrice)
}
if (soldOnOrAfter '= "") {
    do qb.Add("and DateOfSale >= ?", soldOnOrAfter)
}
set sc=qb.Execute(.rset)
    

You can use many question marks in one line:

do qb.Add("and DateOfSale between ? and ?", soldAfter, soldBefore)

Best regards.