dynamic sql for select statement in procedure

[former_member330120]

Hi

i would like to use a dynamic select statment within a hana procedure.

the select statement should use value of the variable v_name as data field of the table DEBT.

like this: (But this shows only the value GENDER in the resut)

create type t_woe as table (I_GENDER nvarchar);

procedure get_amount ( out v_woe t_woe )

sqlscript as

begin

v_name char(10) := 'GENDER';

v_woe = select :v_name as I_GENDER from DEBT;

end;

Thanks in Advance

Mike

[acaireta · Accepted solution]

This is the correct Procedure syntax,

create procedure get_amount ( out v_woe t_woe )

LANGUAGE sqlscript as

begin

declare v_name char(10) := 'GENDER1';

v_woe = select :v_name as I_GENDER from DEBT;

end;

If I have not answered your question, please give details.

Regards.

[former_member330120 · Accepted solution]

Hi Adria,

thanks for your quick response.

Unfortunately it does not work.

your proposed solution delivers for the column "I_GENDER'  the values 'GENDER' which looks like this

          I_GENDER

1        GENDER

2        GENDER

3        GENDER

....      .....

10      GENDER

any other idea?

kind regards

Mike

[acaireta · Accepted solution]

Excuse me now I understand.

To perform complex SQL Dynamics must compose the SQL statement as a string and execute it with EXEC.

In your case could use a temporary table to store the data and return into the OUT-variable.

Example:

DROP type t_woe;

create type t_woe as table (I_GENDER nvarchar);

DROP procedure get_amount;

create procedure get_amount ( out v_woe t_woe )

LANGUAGE sqlscript  as

begin

declare v_name char(10) := 'GENDER';

CREATE LOCAL TEMPORARY TABLE #TEMP_GET_AMOUNT LIKE t_woe;

EXEC 'INSERT INTO #TEMP_GET_AMOUNT select "' || :v_name || '" as I_GENDER from DEBT';

v_woe = SELECT * FROM #TEMP_GET_AMOUNT;

DROP TABLE #TEMP_GET_AMOUNT;

end;

CALL get_amount(null);

I hope it helps

Regards.

[former_member182302 · Accepted solution]

Hi Mike,

As Adira mentions we need to use "EXEC" to execute the "SELECT" statement formed dynamically.

But my points here,

1) You may not want to insert ( operation takes some time) into "Temporary" table and then get the results.

2) Also it occupies more space (more memory consumed)

3) Possibility of locking when multiple users try to call this procedure ( this point is my view as per experience on other databases would love to have SCN experts here to comment on the point if they see this)

Hence based on logic ( like the way you are filtering , joining and aggregation takes place) i would rather suggest you to use EXEC IMMEDIATE approach ( which also gives flexibility to get dynamic output) as mentioned in one of the blogs i have written below:

Do check and let me know.

Regards,

Krishna Tangudu

[acaireta · Accepted solution]

Hi Krishna Tangudu,

I congratulate him on his blog, I wonder if there are any way to store the result of dynamic SQL into a local/out variable.

Something like this:

V_OUT: = EXECUTE IMMEDIATE (: SQL_STR);

Regards.

[former_member182302 · Accepted solution]

Hi Adria,

Thank you for your kind words.

For your question, No you cannot assign a table variable to EXECUTE IMMEDIATE.

It will result in syntax errors.

Regards,

Krishna Tangudu

[former_member330120 · Accepted solution]

Hi Krishna,

Great ! Works fine !

Thanks for you support

Regards

Mike

[Former Member · Accepted solution]

Hi Adria,Krishna & Mike

Could you please let me how to  pass the result of dynamic SQL into a local/out variable?

What would be the syntax?

Regards

Sayan

[acaireta · Accepted solution]

Hi Sayan,

SQL dynamic not support the INTO statment for save local/out variable.

Please view this post, in it your find a workaround: http://scn.sap.com/thread/3430543

Regards.

[former_member182302 · Accepted solution]

Hi Sayan,

Please find the error message you will get when you try to call a READ-WRITE procedure from READ ONLY procedure:

But you can call a READ-WRITE procedure from another READ-WRITE procedure and capture the output as shown below:

DROP PROCEDURE GBI_TD_OPS.NESTED_SP;
CREATE PROCEDURE GBI_TD_OPS.NESTED_SP
(
    OUT SQL_TABLE TABLE_TYPE ) LANGUAGE SQLSCRIPT AS

BEGIN 

CALL GBI_TD_OPS.SP_TEST
(SQL_TABLE);

END
;

call GBI_TD_OPS.NESTED_SP (?);

The above syntax works and you can send the output (Table) from one procedure to another.

Regards,

Krishna Tangudu