How to Pass Database Name & Table Name as Input Parameter to Stored Procedure

Database

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 Procedure
Is this possible...Query look like this

replace 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
2 REPLIES
Teradata Employee

Re: How to Pass Database Name & Table Name as Input Parameter to Stored Procedure

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

Re: How to Pass Database Name & Table Name as Input Parameter to Stored Procedure

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