SAP HANA Execute Immediate with [INTO ]

[Former Member]

The Documentation for Execute Immediate unfortunately does not show how to use the into statement.

So how can i store the result of a dynamic SQL in a variable?

Background:

I need dynamic SQL since the table name and columns are variables like this:

dynSql := 'SELECT max(' || searchColName || ') ' || char(10);
dynSql := dynSql || 'FROM ' || tbl || char(10);
dynSql := dynSql || 'WHERE ' || whereColName || ' = ''' || whereColValue || '''';

execute immediate dynSql;-- into outputValue;

The variable content could be:

select max(TIMESTAMP)
from "SCHEMA"."TABLE"
where "UniqueId" = 'XXX123XX-EA9E-43F4-AB27-104B3E4DXXXX';

So how can i store the value of TIMESTAMP in the output variable of the stored proceedure?

[former_member483366]

Hello, it is not possible to insert the result in a self-defined temporary table (table_var = EXECUTE IMMEDIATE SQLstring), but if it is possible to insert the result in a declared table, now, this is a great limitation of HANA because it conditions the result of the dynamic query by structure declared in the table.

[pfefferf]

I think the documentation is already quite clear regarding that (also not providing a code example). But you can check the blog here for an example.

Regards,
Florian

PS: I assume you have already a HANA 2.0 SPS01 system to have this feature supported.

[Former Member]

OK that's the reason why it's not working.
We do not have HANA 2.0
Thx for the hint.

How to do it with HANA 1.0
with a temporary table?

[pfefferf]

Jep, with all the overload coming with it.

[robert_schmidt01]

I see the blog, but not letting me do it:

EXECUTE IMMEDIATE 'SELECT NEW ST_Point(' || char(39) || 'POINT(' || decEndPointLong1 || ' ' || decEndPointLat1 || ')' || char(39) || ', 4326).ST_Distance( NEW ST_Point(' || char(39) || 'POINT(' || CURRLONG || ' ' || CURRLAT || ')' || char(39) || ', 4326)) FROM DUMMY' INTO dDistEP1;

Any ideas?

[lbreddemann]

You may want to create a new question for your issue robert.schmidt01 .

Make sure to include the error message you get and enough code & test data to reproduce the issue.
From that single statement alone no analysis is possible.