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).
I think the other commenters clarified it all, but I thought I'd add a little more.
Try this variant, with the variable x intentionally undefined.
write (1=0) && (x = 0)
0
Since 1=0 is 0, you don't get an <UNDEFINED> for x, since the right-hand (x=0) expression is ignored, and the entire statement is 0. Now try this:
write 1=0 && x=0
1
which, as @Roger Merchberger showed, is really
write (((1=0) && x) = 0)
Since 1=0 is 0, you again don't get an <UNDEFINED> for x, since the x expression (only) is ignored, and the ((1=0) && x) expression is 0. Finally, 0=0 is 1.
...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").