Give it a try on SQLBuilder tool

What about this dynamic discarding of SQL criteria based on empty parameters extending %SQLQuery?
 

Class gen.SmartSQLQuery Extends%Library.SQLQuery
{

ClassMethod Func() As%SQL.StatementResult [ CodeMode = generator, ProcedureBlock = 1, ServerOnly = 1 ]
{
    set%code=0// don't generate any code if it not for a queryif%mode="method"quit$$$OK// %mode is "propertymethod" for a valid query. We don't have any way currently to detect a misuse of a query classif '$$$comMemberDefined(%class,$$$cCLASSquery,%property) quit$$$OK// Reset the formal list to the query declaration:$$$comSubMemberKeyGetLvar(formal,%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHformalspecparsed)
    $$$comMemberKeyGetLvar(qformal,%class,$$$cCLASSquery,%property,$$$cQUERYformalspecparsed)
    $$$comSubMemberKeySet(%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHformalspecparsed,formal_qformal)
    Set glbArgList = formal_qformal
    Set publicList = ""For i=1:1:$ListLength(glbArgList) {
        Set$Piece(publicList,",",i) = $List($List(glbArgList,i),1)
    }
    Set publicList = publicList _ "," _ "tStatement" _ "," _ "tResult"$$$comSubMemberKeySet(%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHpubliclist,publicList)
    set sc=$$SetOneQueryMeth^%occQuery(%class,%property,%method) quit:$$$ISERR(sc) sc

    $$$comMemberKeyGetLvar(origin,%class,$$$cCLASSquery,%property,$$$cXXXXorigin)
    $$$comMemberKeyGetLvar(query,%class,$$$cCLASSquery,%property,$$$cQUERYsqlquery)
        // preparse the query to construct the actual argument list. If more than the supported number of arguments then revert to// the non-dynamic optionset query = $zstrip(query,"<W")
    set tLines = 0for tPtr = 1:1:$Length(query,$$$NL) { set tLine = $Piece(query,$$$NL,tPtr) if tLine '= "" { set tLines = tLines + 1, tLines(tLines) = tLine } }
    set sc=$$ExpandMacros^%SYS.DynamicQuery(%class,.tLines) QUIT:$$$ISERR(sc) sc
    set SQLCODE = $$dynamic^%qaqpreparser(.tLines,.tStatementPreparsed,.tStatementArgs)
    
    $$$GENERATE($Char(9)_"try {")
    $$$GENERATE($Char(9,9)_"Set query = """_$replace(query,$$$NL,"""_$C(13,10)_""")_"""")
    $$$GENERATE($Char(9,9)_"For i=1:1:$Length(query,$$$NL) {")
    $$$GENERATE($Char(9,9,9)_"Set line=$Piece(query,$$$NL,i)")
    $$$GENERATE($Char(9,9,9)_"If line?.E1"":""1.AN {")
    $$$GENERATE($Char(9,9,9,9)_"Set var=$Piece($Piece(line,"":"",2),"" "",1)")
    $$$GENERATE($Char(9,9,9,9)_"if @var="""" {")
    $$$GENERATE($Char(9,9,9,9,9)_"Set $Piece(query,$$$NL,i) = ""-- ""_line")
    $$$GENERATE($Char(9,9,9,9)_"}")
    $$$GENERATE($Char(9,9,9)_"}")
    $$$GENERATE($Char(9,9)_"}")
    $$$GENERATE($Char(9,9)_"set tLines = 0 for tPtr = 1:1:$Length(query,$$$NL) { set tLine = $Piece(query,$$$NL,tPtr) if tLine '= """" { set tLines = tLines + 1, tLines(tLines) = tLine } }")
    // $$$GENERATE($Char(9,9)_"set sc=$$ExpandMacros^%SYS.DynamicQuery(%class,.tLines) Throw:$$$ISERR(sc) ##class(%Exception.StatusException).ThrowIfInterrupt(sc)")$$$GENERATE($Char(9,9)_"set SQLCODE = $$dynamic^%qaqpreparser(.tLines,.tStatementPreparsed,.tStatementArgs)")
    $$$GENERATE($Char(9,9)_"//")
    $$$GENERATE($Char(9,9)_"set tSelectMode = """_$Case($$$ucase(%parameter("SELECTMODE")), "RUNTIME": "", "ODBC": 1, "DISPLAY": 2, "LOGICAL": 0, : "")_"""")
    $$$GENERATE($Char(9,9)_"if SQLCODE=0 && ($Listlength(tStatementArgs) < 361) && ($Length(tStatementPreparsed) < 40000) {")
    $$$GENERATE($Char(9,9,9)_"set tExecuteArgs = """" for tPtr=1:2:$ListLength(tStatementArgs) { set tArg = $Case($List(tStatementArgs,tPtr),""?"":""$g(%parm(""_$Increment(qcount)_""))"",""c"":$$quoter^%qaqpreparser($List(tStatementArgs,tPtr+1)),""v"":""$g(""_$List(tStatementArgs,tPtr+1)_"")"",:"""") Set tExecuteArgs = tExecuteArgs _ "","" _ tArg }")
        $$$GENERATE($Char(9,9,9)_"set tSchemaPath = ##class(%SQL.Statement).%ClassPath($classname())")
        $$$GENERATE($Char(9,9,9)_"set tStatement = ##class(%SQL.Statement).%New(tSelectMode,tSchemaPath)")
        $$$GENERATE($Char(9,9,9)_"do tStatement.prepare(tStatementPreparsed)")
        $$$GENERATE($Char(9,9,9)_"Xecute ""set tResult = tStatement.%Execute(""_$Extract(tExecuteArgs,2,*)_"")""")
    $$$GENERATE($Char(9,9)_"}")
    $$$GENERATE($Char(9)_"}")
    $$$GENERATE($Char(9)_"catch tException { if '$Isobject($Get(tResult)) { set tResult = ##class(%SQL.StatementResult).%New() } set tResult.%SQLCODE=tException.AsSQLCODE(),tResult.%Message=tException.AsSQLMessage() }")
    $$$GENERATE($Char(9)_"Quit tResult")
    QUIT$$$OK
}

}

Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(:Name IS NULL or Name %STARTSWITH :Name)
AND
(:Age IS NULL or Age >= :Age)
}

This kind of query ends up preventing Caché SQL compiler from optimizing using index based on each of the criteria made optional.

What is your conclusion based on? If you check the plans of this query for different combinations of parameters, then the corresponding indexes are used (it is assumed that the table was previously configured via TuneTable).

select * from Sample.SP_Sample_Filter_By('s')
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(NULL IS NULL or Age >= NULL)

select * from Sample.SP_Sample_Filter_By(,47)
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(NULL IS NULL or Name %STARTSWITH NULL)
AND
(47 IS NULL or Age >= 47)

select * from Sample.SP_Sample_Filter_By('s',47)
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(47 IS NULL or Age >= 47)