All Forums Database
maheshkanni 9 posts Joined 02/12
12 Jun 2015
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,tabl ename);
This above statement should internally call StoredProcedure2 like
Call StoredProcedure2(databasename,tablename);
 
Kindly help.
 
Regards,
Mahesh
 

dnoeth 4628 posts Joined 11/04
12 Jun 2015

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;

 

Dieter

gskaushik 56 posts Joined 09/10
12 Jun 2015

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

Regards
Subramanian kaushik Gurumoorthy

maheshkanni 9 posts Joined 02/12
15 Jun 2015

Thank you dnoeth for the workaround.

maheshkanni 9 posts Joined 02/12
15 Jun 2015

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

You must sign in to leave a comment.