In SQLScript, how to use EXEC to call another procedure with parameters in procedure？

[Former Member]

Hi experts,

In SQLScript, How to use EXEC to call another procedure with input and output parameters in procedure？thanks very much

[sagarjoshi · Accepted solution]

Not sure what is your problem in calling procedure. Did you check SQLScript guide

http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

Here is example

with 2 input parameters (first is a scalar and second is table input)

Output parameter is table which you can use as :OUT_TAB in rest of procedure coding.

CALL MYPROC(:IN_SCALAR1, :IN_TAB1, OUT_TAB);

[Former Member · Accepted solution]

Hi Sagar,

thank you! I generate another procedure with an input parameter and an output parameter in a procedure. Then i need to call the generated procedure using EXEC. Here is my code:

create procedure ftest1(out sum_num bigint)

as

begin

declare fa_output bigint;

declare v_sql_drop varchar(200);

declare v_sql varchar(500);

declare cursor c_cursor1 for select num from TABLE1;

--v_sql_drop := 'drop procedure fe';

--exec v_sql_drop;

v_sql := 'create procedure fe(in i_num bigint,out o_num bigint) as begin';

v_sql := :v_sql || ' o_num := :i_num * 2 + :i_num * :i_num;';

v_sql := :v_sql || ' end';

exec v_sql;

open c_cursor1;

for c_item as c_cursor1 do

exec 'call fe(c_item.num,o_num=>fa_output)';

if sum_num is null then

sum_num := fa_output;

else

sum_num := :sum_num + fa_output;

end if;

end for;

close c_cursor1;

end;

The underline code is using exec to call the generated procedure. But this method cannot work. Any suggestion? thanks again!

[sagarjoshi · Accepted solution]

As documented in SQLScript guide

In Dynamic SQL

You cannot use SQLScript variables in the SQL statement.

You cannot bind the result of a dynamic SQL statement to a SQLScript variable.

This the problem you are facing.

May be you should explain your functional requirement and why you are trying to generate dynamic SQL and procedure and may be there could be some help and better way to solve this problem,

[Former Member · Accepted solution]

Sagar, thanks. I solved the problem by using an alternative method.