All Forums Database
30 Apr 2008
How to Pass Database Name & Table Name as Input Parameter to Stored Procedure

Hi Guys,I have a requirement where I need to pass the Database Name & Table Name as input parameter to the Stored ProcedureIs this possible...Query look like thisreplace PROCEDURE ISMAIMH.GET_ITEM_COUNT (IN db_name varchar(20), IN tbl_name varchar(20), OUT SP_RESULT integer) BEGIN SELECT COUNT(*) INTO: SP_RESULT FROM :db_name.:tbl_name; END;-->> SELECT * FROM :db_name || '.' || :tbl_name;Can someone help me out with this...how can i achieve this..Thanks

Adeel Chaudhry 773 posts Joined 04/08
21 May 2008

You can use dynamic sql to do this and dump the output variable in some VT table, and after executing dynamic sql, you can get the return value from that table and return it.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

atiqullh 1 post Joined 01/14
08 Jan 2014

Salam Mr.Adell,
I hope you are fine and doing well.
could you please tell me how to use select from variable as a table_name in stored procedure?
for example:

REPLACE PROCEDURE DD_FUNC.STEP2()

BEGIN 

DECLARE  Begin_date DATE;

DECLARE  end_date DATE;

DECLARE prefix_table_name  VARCHAR(30);

 

 

SELECT ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1), -3) INTO Begin_date;

SELECT ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1,0) - 92  INTO end_date;

 

SELECT 'DD_TAB.ib_cdr_st_'|| CAST ((CAST(CURRENT_DATE-80 AS FORMAT 'mmm')) AS CHAR(3)) 

INTO prefix_table_name;

 

 

INSERT INTO DD_TAB.test1

SELECT * FROM :prefix_table_name

WHERE activity_start_dt>=:Begin_date

and activity_start_dt<=:end_date;

END;

 

 

Please contact my Email Address: amehrin@etisalat.af

 

thanks in advance,

atiqullah

 

You must sign in to leave a comment.