...and it makes sense that using $listbuild to add at the end would be better than the other two options, which are general purpose ("find the position in the list and put something there").
As @Yaron Munz and @Alexander Koblov correctly pointed out, you can use Embedded SQL or Dynamic SQL. You can also use Class Queries.
Using Embedded SQL, host variables in IRIS (:varname) can be inputs or outputs, just like host variables in SQL Server and Oracle, but without any DECLARE statement. Host variables used as inputs are automatically sanitized. Examples:
WHERE amount > :var1 INSERT INTO ... VALUES (:var1, :var2, :var3) SELECT ... INTO :var1, :var2 FROM ...
Dynamic SQL allows ? placeholders for inputs only instead of host variables, also automatically sanitized. Examples:
WHERE amount > ? INSERT INTO ... VALUES (?, ?, ?)
Dynamic SQL returns output values in the result set object it returns, which you can access and copy into variables. Examples:
set resultSet = ##class(%SQL.Statement).%ExecDirect(, sql, values for any placeholders) while resultSet.%Next() { set var1 = resultSet.column1 set var2= resultSet.column2 }
Class queries use host variables (:varname) for inputs (like Embedded SQL), and return output values in the result set object (like Dynamic SQL).







Can also be used as #include inside a method (applies only to that method)