Question
· Jan 27

SET VALUE - CACHE SQL

Hello,

It's possible to set a value to a "variable" in SQL Cache?

Like in SQl Server or Oracle?

Thanks

Product version: Caché 2018.1
Discussion (6)3
Log in or sign up to continue

Hello Caio,

There is no DECLARE @variable (SQL server) or DECLARE variable (Oracle) on Cache but there are few options:

1. Use a host variable(s) in embedded SQL:

SET variable = 2000
&SQL(SELECT Column FROM Table WHERE ID = :variable) 

2. Use the same with Dynamic SQL

SET variable = 2000
SET sql = "SELECT Column FROM Table WHERE ID = " _ variable
DO ##class(%SQL.Statement).%ExecDirect(, sql)

3. Writing a SP

CREATE PROCEDURE Procedure(IN variable  INT)
AS
BEGIN
    SELECT Column FROM Table WHERE ID = variable;
END

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).