Dynamic SQLScript in Hana on dynamic table

[Former Member]

Hi all,

I am reading the Hana SQL Script reference but I can't find any reference on how to create a procedure in sql script that receive in input a TABLE NAME and execute some operation on that table .

Like that :

I have 2 DB table as SOURCE_TABLE_NAME and TARGET_TABLE_NAME having the same structure

PROCEDURE1 - ( INPUT SOURCE_TABLE_NAME and INPUT  TARGET_TABLE_NAME )

(

   TRUNCATE TARGET_TABLE_NAME;

   INSERT INTO TARGET_TABLE_NAME FROM ( SELECT * FROM SOURCE_TABLE_NAME )

)

Then, if a call :

call PROCEDURE1(db_tb_a, db_tb_b)

the effect is that i have in  db_tb_b the same record of db_tb_a .

thank you

[former_member184768 · Accepted solution]

Hi Carlo,

I think there is some gap in dynamic SQL understanding. You cannot pass TABLE as input parameter to the procedure, but you can pass table name as varchar value to the procedure.

Secondly TRUNCATE doesn't work in Dynamic SQL. .

You may have to use DELETE FROM.

You can use the following procedure code:

create procedure pr_copy_data (in v_from_table varchar(20), in v_to_table varchar(20))

as

begin

exec 'delete from '||:v_to_table;

exec 'insert into '||:v_to_table||' select * from '||:v_from_table;

end;

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

after generating this dynamic SQL i want store it in a new stored procedure .

How can i generate a new stored procedure from a stored procedure ?

thank you

[former_member184768 · Accepted solution]

Hi Carlo,

You can generate the required strings (statements) like drop procedure, create procedure and the logic you need.

The following basic code works fine.. Please adjust it as per your requirements.

create procedure pr_copy_data1 (in v_from_table varchar(20), in v_to_table varchar(20))

as

v_sql varchar(200);

begin

--exec 'drop procedure pr_copy_data';

v_sql := 'create procedure pr_copy_data_a ()

as

begin ';

v_sql := :v_sql || ' exec ''delete from '||:v_to_table||''';';

v_sql := :v_sql || ' exec ''insert into '||:v_to_table||' select * from '||:v_from_table||''';';

v_sql := :v_sql || 'end;';

--exec v_sql;

insert into my_log values (:v_sql);

end;

Regards,

Ravi

[Former Member · Accepted solution]

Just FYI - it is possible to pass tables directly into procedures as long as they have the same structure as the table types defined for respective parameters.

However, this capability isn't particularly helpful for the use case defined here originally (i.e. some kind of ETL work) given the hard-coded structure required.

Here's an example of passing a table directly:

DROP TYPE TT_TEST;

CREATE TYPE TT_TEST AS TABLE (F INTEGER);

DROP TABLE TABLE_TEST;

CREATE COLUMN TABLE TABLE_TEST (F INTEGER);

INSERT INTO TABLE_TEST VALUES (1);

DROP PROCEDURE PROC_TEST;

CREATE PROCEDURE PROC_TEST(IN var_in TT_TEST) AS

BEGIN

          SELECT * FROM :var_in;

END;

CALL PROC_TEST(TABLE_TEST);

[former_member223209 · Accepted solution]

Hi Ravi,

Is there any way by which we can store the output of EXEC statement in one variable?

so that on that output we can apply other CE function like projection

this line execute fine but not storing any output EXEC ('SELECT * FROM ' || :test1 || ';');

But when i am  trying below code but it is giving me error

temp =  EXEC ('SELECT * FROM ' || :test1 || ';');

Kindly suggest with some code.And one condition is that we don't know metadata in advance.

Thanks

Bhupender

[praveen_nair · Accepted solution]

This message was moderated.