I have a requirement that I need to call SP dynamically.
Replace Procedure DB.StoredProcedure1(IN SP_NAME VARCHAR(30),IN DB_NAME VARCHAR(15),IN TBL_NAME VARHCAR(30))
Call SP_NAME(DB_NAME,TBL_NAME); /* I need to call SP_NAME which is passed as param */
If I run the below statement, It is giving Error: Stored Procedure SP_NAME doesnot exist.
This above statement should internally call StoredProcedure2 like
there's no way to do what you want in a procedure.
The only workaround is
WHEN 'StoredProcedure1' THEN CALL StoredProcedure1(DB_NAME,TBL_NAME);
WHEN 'StoredProcedure2' THEN CALL StoredProcedure2(DB_NAME,TBL_NAME);
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);
Step 2 Then execute the bteq file to call the procedure.
Do check and revert if it is working ?
Subramanian kaushik Gurumoorthy
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.
I tried to use dnoeth's CASE workaround inside a stored procedures but just got errors no matter how I arranged it. I ended up using an IF with a series of ELSEIF statements:
IF SProc_ID = 1 THEN CALL StoredProc_1(); ELSEIF SProc_ID = 2 THEN CALL StoredProc_2(); ... END IF ;
Your IF translates to
CASE SProc_ID WHEN 1 THEN CALL StoredProc_1(); WHEN 2 THEN CALL StoredProc_2(); END CASE;
This should not result in errors.
Ah!, END CASE .
I had not seen an END CASE clause before (I always just used END as described in the TD online documentation and your original example). I guess I never used it outside of a SELECT. Adding the CASE makes all the difference!
Yup, I forgot the END CASE in my example.
It's similar to IF - END IF, at least not IF - FI and CASE - ESAC :-)
I don't know why the syntax in SQL is different from the syntax in SPL, ask those guys in the Standard SQL commitee...