Using Custom Bind Variables for View Criteria Used Multiple Times

Often you may need to execute the same view object multiple times within the same script.

If your operand values change from query execution to query execution, then named bind variables allow you to append a view criteria once, and use it many times with different values for the criteria expression operands. Just add one or more named bind variables to your view object, and then set the values of these bind variables as appropriate before each execution. The bind variables act as "live" placeholders in the appended filter expression, and their current values are used each time the view object's query is executed.

To add a named bind variable, use the addBindVariable() function. Pass a view object or rowset as the first argument and a string value to define the name of the bind variable as the second argument as shown in the example below. You can name your bind variable using any combination of letters, numbers, and underscores, as long as the name starts with a letter. When using a view criteria with a standard object like the StaffMember in this example, it is best practice to name your bind variables with the same suffix (_c) as custom objects and custom fields use.

def vo = newView("StaffMember")
addBindVariable(vo,"VarLastName_c")
setBindVariable(vo,"VarLastName_c","King")
vo.appendViewCriteria("LastName = :VarLastName_c")
vo.executeQuery()
while (vo.hasNext()) {
  def r = vo.next();
  // Will return "Steven King" and "Janette King"
}
setBindVariable(vo,"VarLastName_c","Higgins")
vo.executeQuery()
while (vo.hasNext()) {
  def r = vo.next();
  // Will return "Shelley Higgins"
}

You can reference a named bind variable in the view criteria expression anywhere a literal value can be used, prefacing its name by a colon (e.g. :VarLastName_c). After adding the bind variable, you use the setBindVariable() function one or more times in your script to assign values to the variable. Until you explicitly set its value for the current view object or rowset, your bind variable defaults to having a value of null. Accidentally leaving the value null will result in retrieving no rows for most filter expressions involving a bind variable operand due to how the SQL language treats the null value in comparisons. The current value of the bind variable is used each time your script executes the view object. In the example below, this causes the rows for employees "Steven King" and "Janette King" to be returned during the first view object execution, and the row for "Shelly Higgins" to be returned on the second view object execution.

By default, the data type of the named bind variable is of type Text. If you need to use a bind variable in filter expressions involving number, date, or datetime fields, then you need to explicitly define a bind variable with the appropriate type for best performance. To add a bind variable of a specific datatype, pass one of the values Text, Number, Date, or Datetime as a string value to the optional third argument of the addBindVariable() function. For example, the following script uses two bind variables of type Number and another of type Date. Notice that the data type name is not case-sensitive (e.g. Number, number, or NUMBER are all allowed).

def vo = newView('TroubleTicket_c')
addBindVariable(vo,"VarLowPri_c","number")
addBindVariable(vo,"VarHighPri_c","Number")
addBindVariable(vo,"VarDueDate_c","DATE")
setBindVariable(vo, "VarLowPri_c", 1)
setBindVariable(vo, "VarDueDate_c", 2)
setBindVariable(vo, "VarDueDate_c", today() + 3)
vo.appendViewCriteria("(Priority_c between :VarLowPri_c and :VarHighPri_c) and DueDate < :VarDueDate_c ")
vo.executeQuery()
while (vo.hasNext()) {
  def row = vo.next()
  // Returns trouble tickets with priorities 1 and 2 that are
  // due within three days from today
}
setBindVariable(vo, "VarLowPri_c", 3)
setBindVariable(vo, "VarDueDate_c", 4)
setBindVariable(vo, "VarDueDate_c", today() + 5)
vo.executeQuery()
while (vo.hasNext()) {
  def row = vo.next()
  // Returns trouble tickets with priorities 3 and 4 that are
  // due within five days from today
}