Need variable number of query parameters in dynamic query

Question

Laura Cavanaugh · Jul 17, 2017

I have a query string that I am creating programmatically, based on some user inputs. The user might search on 5 fields, or 8 fields, or no fields.

In my sql statment, some of these fields require parameters in the %Execute statement.

For example:

if user picks lastname, sql = "select * from person where lastname = ?"

if user also picks age, sql = "select * from person where lastname=? and age > ?"

I then have these lines of code to create my result set:

set statement = %SQL.Statement

statement.%Prepare

resultset = statement.%Execute(param1, param2)

-- but it might be

resultset = statement.%execute(param1)

How can I pass a variable number of parameters when I don't know how many the user will pick to filter the search? I need some kind of parameter object? Or will indiraction work? Something with $lists? %ListOfDataTypes?

parameterObject.Insert(param1)

parameterObject.Insert(param2)

resultset = statement.%Execute(parameterObject)

Does anything like this exist?

#SQL #Caché