SET VALUE - CACHE SQL

Question

Question

Caio Timm · Jan 27

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 (4)2

Log in or sign up to continue

score 0 · Answer 1 · 2025-01-28T09:07:55-05:00

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

score 3 · Answer 2 · 2025-01-30T03:57:16-05:00

Hi Yaron.

SQL Injection police here ;-)

You can pass query parameters to %ExecDirect as well

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

score 1 · Answer 3 · 2025-01-30T05:05:55-05:00

Hi Alexander.

Sorry for being pedantic, but you're missing the ending quotation mark when setting the value of 'sql'

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

score 1 · Answer 4 · 2025-01-30T05:43:48-05:00

Thanks! Fixed!

1 0