Retrieve data from calling stored procedure using "EXEC" command

[former_member281438]

Hi,

    I am trying to execute the stored procedure dynamically using EXEC command inside another procedure. Here my doubt is how to retrieve the result after dynamic call of procedure. Please help me on how to retrieve the result of stored procedure.

Here "SYSTEM"."ZPERIOD_TEST" is temporary global table.

CREATE PROCEDURE test_procedure(out lt_data1 tt_data )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA SYSTEM AS  

BEGIN

/*****************************

  Write your procedure logic

*****************************/

      declare lv_sql nvarchar( 1000);

          lv_sql := 'CALL "_SYS_BIC"."Example.First_work/test_period"( lt_data )';

          EXEC :LV_SQL;

     LV_SQL := 'INSERT INTO "SYSTEM"."ZPERIOD_TEST" SELECT * FROM :lt_data';

     EXEC :LV_SQL;

     lt_data1 = select * from "SYSTEM"."ZPERIOD_TEST";

END;

i got below error when calling above procedure

Could not execute 'call "_SYS_BIC"."Example.First_work/test_procedure"( lt_data1 => ? )' in 326 ms 61 µs .

SAP DBTech JDBC: [1281]: wrong number or types of parameters in call:  [1281] "_SYS_BIC"."Example.First_work/test_procedure": line 23 col 11 (at pos 963): [1281] (range 3) wrong number or types of parameters in call exception: wrong number or types of parameters in call: Physical table is not allowed in OUT table variable position: LT_DATA: line 1 col 51 (at pos 50)

Thanks,

Venkatesh

[former_member281438]

Hi,

    I achieved this with below code.

Here "SYSTEM"."ZPERIOD_TEST" is temporary global table.

CREATE PROCEDURE test_procedure(out lt_data1 tt_data )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA SYSTEM AS 

BEGIN

/*****************************

  Write your procedure logic

*****************************/

      declare lv_sql nvarchar( 1000);

          lv_sql := 'CALL "_SYS_BIC"."Example.First_work/test_period"( "SYSTEM"."ZPERIOD_TEST")                                    WITH OVERVIEW';

          EXEC :LV_SQL;

     lt_data1 = select * from "SYSTEM"."ZPERIOD_TEST";

END;

Thanks

Venkatesh