Need variable number of query parameters in dynamic query
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?