Dynamially call a Stored Procedure within a stored Procedure

[praveen_nair]

Hello,

I want to call a Stored Procedure from another Stored Procedure, dynamically. I have pasted a code snippet below:

Eg: I have few Stored Procs as below:

CREATE PROCEDURE CAL_DISCOUNT(IN value1 INTEGER, IN disc INTEGER) AS

BEGIN

...

..
.

END;

CREATE PROCEDURE CAL_DISCOUNT_V1(IN value1 INTEGER, IN disc INTEGER) AS

BEGIN

...

..
.

END;

Now I have my main Stored Proc which takes the name of the procedure to call as an input:

CREATE PROCEDURE MY_PROC( IN dyn_proc_name nvarchar(50) AS

BEGIN

...

CALL dyn_proc_name (:var1, :var2);

..

...

END;

I want to now call the main proc as below:

CALL MY_PROC('CAL_DISCOUNT');

This should call CAL_DISCOUNT and pass 2 values (defined within the PROCEDURE MY_PROC) dynamically. I was able to call a Procedure using EXECUTE IMMEDIATE when the procedure did not take any input. But when I call a procedure and pass an input it fails. I tried 'USING' syntax with EXECUTE IMMEDITATE, but it was throwing error.

Any pointers?

Regards,

[pfefferf]

Hello Parveen,

as you already described, a dynamic call of a procedure is only possible using dynamic SQL. Parameter values out of SQLScript variables can only be transferred directly in the dynamic SQL if the values are scalar and character typed. So the parameters can be included by concatenation in the dynamic statement. Table typed values cannot be addressed in the dynamic SQL (here it would be an option to use local temporary tables).

But the overall question is, why you need such a dynamic call which brings several disadvantages. For instance bad performance due to less or no optimiziation. Another point with dynamic SQL using variables is that you have to consider SQL injection vulnerability.

Regards,

Florian

[praveen_nair]

Hello Florian,

Thanks for the reply. Can you share an example where you mentioned:

Parameter values out of SQLScript variables can only be transferred directly in the dynamic SQL if the values are scalar and character typed. So the parameters can be included by concatenation in the dynamic statement. Table typed values cannot be addressed in the dynamic SQL (here it would be an option to use local temporary tables).

I want to read the output from the dynamic procedure.

I understand the risk for SQL Injection, but my scenario is taken care of . Looking forward for you reply.

Regards

[pfefferf]

Here is a simple example, transfering a nvarchar value to a procedure in a dynamic call:

  declare lv_value nvarchar(256) := 'Test Value';
  declare lv_sql string;

  lv_sql := 'CALL "P_MISC" (''' || :lv_value || ''')';

  exec :lv_sql;

[praveen_nair]

I want to read the value. My Procedure (Ex: P_MISC.. ) returns a value. How do I modify the above code of your to get the output to the parent Procedure?

[pfefferf]

Not possible with that approach.