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

8 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

Diz
Fan

Re: Dynamic calling of stored procedure.

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 ;
Junior Contributor

Re: Dynamic calling of stored procedure.

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.

 

Diz
Fan

Re: Dynamic calling of stored procedure.

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!

Thanks dnoeth!

Junior Contributor

Re: Dynamic calling of stored procedure.

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...