Dynamic calling of stored procedure.

Database
Enthusiast

Dynamic calling of stored procedure.

Hi All,

I have a requirement that I need to call SP dynamically.

E.g:

Replace Procedure DB.StoredProcedure1(IN SP_NAME VARCHAR(30),IN DB_NAME VARCHAR(15),IN TBL_NAME VARHCAR(30))

BEGIN

Call SP_NAME(DB_NAME,TBL_NAME); /* I need to call SP_NAME which is passed as param */

END;

If I run the below statement, It is giving Error: Stored Procedure SP_NAME doesnot exist.

Call DB.StoredProcedure1(StoredProcedure2,databasename,tablename);

This above statement should internally call StoredProcedure2 like

Call StoredProcedure2(databasename,tablename);

Kindly help.

Regards,

Mahesh

4 REPLIES
Junior Contributor

Re: Dynamic calling of stored procedure.

Hi Mahesh,

there's no way to do what you want in a procedure.

The only workaround is

 CASE SP_NAME
WHEN 'StoredProcedure1' THEN CALL StoredProcedure1(DB_NAME,TBL_NAME);
WHEN 'StoredProcedure2' THEN CALL StoredProcedure2(DB_NAME,TBL_NAME);
...
END;
Enthusiast

Re: Dynamic calling of stored procedure.

I think it can be done but not through sql

Step 1 We can get the stored procedure to be executed at run time in a file and do create a bteq file with call statement as below

.LOGON localtd/tduser, tdpass;
CALL sp_emp('Rufus', 12345);
.LOGOFF
.EXIT

Step 2 Then execute the bteq file to call the procedure.

Do check and revert if it is working ?

regards,

Subramanian kaushik Gurumoorthy

Enthusiast

Re: Dynamic calling of stored procedure.

Thank you dnoeth for the workaround.

Enthusiast

Re: Dynamic calling of stored procedure.

Hi Kaushik,

Thanks for the reply.

For the solution you said I always need to change the bteq with my new stored procedure I want to run.

Regards,

Mahesh