You´re right, @Vitaliy Serdtsev 

In my test I had used a particular huge table of people with non-default mapping (legacy globals) and with a very specific and custom name index for which nvl(:Name,'') = '' inhibited the index.
But with this plain :Name IS NULL it worked fine.

Thanks a lot!

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 Sample.Person
WHERE 
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' 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.
That´s why I followed Paul´s idea and came up with %SQLQuery´s subclass SmartSQLQuery found above which dynamically comments out each criteria which is not applicable.

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 query
    if %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 class
    if '$$$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 option
    set query = $zstrip(query,"<W")
    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 } }
    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
}

}