How to retrieve the results of an EXECUTE IMMEDIATE query call

[Former Member]

Hi all,

I am creating a procedure within HANA, in which I would like to receive a tablename as input parameter to select some data from the certain table.

The only solution I found, was to use EXECUTE IMMEDIATE and concatenate the tablename to a query string.

Unfortunately I have no idea, how to get the result of the query. According to the documentation there must be a "Result Iterator", which can run through the data, but there is no information, how the iterator is called or respectively used.

Has anyone experienced EXECUTE IMMEDIATE or another solution to retrieve data from a dynamic table?

Thank you and best regards,

Fabian

[lbreddemann · Accepted solution]

Hi Fabian,

usually this approach of being 'super-flexible' with your coding is not the right thing to do.

But anyhow, that's your decision,

The "execute immediate" command reference (build into HANA Studio help, by the way) tells us:

EXECUTE IMMEDIATE

Syntax:

EXECUTE IMMEDIATE '<sql-statement>'

Description:

EXECUTE IMMEDIATE

executes the SQL statement passed in a string               argument.

The results of queries executed with

EXECUTE IMMEDIATE are               appended to the procedures result iterator.

[...]

So, the results of the command will be part of the result of the procedure.

By this, you can already tell for what kind of statement the command had been included.

It's to change data, not so much to retrieve data.

For that, you're much better off using standard SQL instead (also, standard SQL can be much better optimized, is safe against SQL injection, and doesn't require special attention like dynamic SQL does).

- Lars

[Former Member · Accepted solution]

Hi Lars

I have a  Requirement where a condition for a select is stored in a Database Table as rule and need to be fetched Dynamically and concatenate with the Query Statement to be executed in Execute Immediate Statement.

Please Suggest a Solution without the use of Execute Immediate Statement.

Regards

Vignesh J

[lbreddemann · Accepted solution]

Have you looked into the APPLY_FILTER() function?

[Former Member · Accepted solution]

Hi Lars

But Problem is i can't pass multiple filter condition to the Function Apply Filter.

Regards

Vignesh J

[lbreddemann · Accepted solution]

Ok, looks like your solution design is based on features that are just not there in SAP HANA right now.

You may want to check out the relatively new HANA Rules Framework for more flexible rule handling.

Anyhow, as the original question had been answered, I'm gonna close this thread now.

- Lars

[Former Member]

Hi Lars, Fabian,

Is it possible to retrieve the results of the 'EXECUTE IMMEDIATE' into some temporary variable which can be used in the sqlscript procedure for further processing similar to what we do here for example:

temp = select <columnlist> from tableX;

then :temp would be used in the sqlscript for further processing ?

I was wondering if we have something like above:

temp = execute immediate  <sql-statement> ;

?

-Dhwanit

[former_member301196]

Hi Dhwanit,

Even I want to know how to store the results of 'EXEC' or 'EXEC IMMEDIATE' into temporary varibles in ABAP Managed Database Procedures(AMDP). Did you get to how this has to be done?

Best Regards,

Madhu

[Former Member]

Hi Fabian,

I haven't worked with EXECUTE IMMEDIATE, but if your procedure has an output table, then you could do the following:

-- 'outer' procedure

CREATE PROCEDURE TEST_PROC AS

BEGIN

     -- assuming you have a procedure with output table called SOME_OTHER_PROC

     CALL SOME_OTHER_PROC(a);

     -- a was populated in procedure above, has same structure as output of that proc.

     -- you can treat it as a table

     SELECT * FROM :a;

END;

[Former Member]

Hi Jody,

thank you for your reply, but unfortutately I have to select from a column table.

Therefore I probably have to use dynamic SQL.

Cheers,
Fabian

[Former Member]

Ah, got it. Misunderstood what you were trying to do. Unfortunately I don't know how to retrieve the results of dynamic SQL in a procedure. Depending on your needs I can provide some Java code to show how to capture them in Java, but that doesn't help much if everything you're doing is in stored procedures...

Cheers,

Jody