'Execute Immediate' with Into Clause in HANA

Question

I have a requirement where-in I need to read a table (table name provided as input parameter of the SP), store the results in a temp table and then store the count of the read table into a variable. Please advise how can this be achieved. I have been able to read the table and its count using dynamic query but am not able to put the results in a temp table/ variable. 'Select' and 'Into' clauses do not seem to be working with 'Execute Immediate'. Thanks.

Answer 1

It is not very clear to me exactly what is being asked, but you should be able to execute a SELECT statement in the following manner:

CREATE PROCEDURE p1(IN tablename VARCHAR) AS
BEGIN
   execute immediate 'SELECT * FROM ' || :tablename;
END;

Then the following statements create a table and call the procedure to retrieve the result:

create table T (i integer);
insert into T values (123);

The following would produce a result set with one row/column with the value 123:

CALL p1('T')

Please note that with this type of functionality, you need to be very careful not to allow any user-provided input to be given directly to a procedure that uses EXECUTE IMMEDIATE to avoid the possibility of SQL injection attacks.