Error while Executing a Dynamic SQL in Teradata Stored Procedure

Database
Fan

Error while Executing a Dynamic SQL in Teradata Stored Procedure

Hi,

I am new to Teradata Stored Procedure. The below is my sample SP. In this am not able to get the desired output(I need to insert table name,column name and count in it to a new table for each column in the input table(to the SP)) but the below code is throwing a run time error (Call Failed 7689: Invalid Dynamic SQL Statement).

Kindly help me in this. This is a little bit urgent requirment.

Code:

REPLACE PROCEDURE t3_rita.CURSOR_SAMPLE (IN v_TABLE_NAME  VARCHAR(100))

BEGIN

DECLARE v_COUNT INTEGER ;

DECLARE my_sql VARCHAR(1000);

DECLARE my_sql_1 VARCHAR(1000);

DECLARE v_COLUMN_NM VARCHAR(100);

DECLARE PROFILE_SAMPLE CURSOR FOR select columnname from dbc.columns

 where tablename='BKEY_ERDM_ACCOUNT'  and databasename='T3_RITA' order by 1;

OPEN PROFILE_SAMPLE;

L1:

LOOP 

FETCH PROFILE_SAMPLE into v_COLUMN_NM;

 IF (SQLSTATE = '02000') THEN

LEAVE L1;

END IF;

set my_sql ='select count(*) into v_COUNT from t3_rita.'||v_TABLE_NAME||';';

EXECUTE IMMEDIATE my_sql;

insert into t3_rita. testing_r (v_TABLE_NAME ,v_COLUMN_NM,v_COUNT); 

END LOOP L1;

CLOSE PROFILE_SAMPLE;

 END;

 

Thanks and Regards,

Raja M

2 REPLIES
Enthusiast

Re: Error while Executing a Dynamic SQL in Teradata Stored Procedure

Enthusiast

Re: Error while Executing a Dynamic SQL in Teradata Stored Procedure

Stored procedure for passing date values

Hi Team, Would you please help me to write a procedure which should pass the parameters of date values. I just wanted use the procedure for passing values rather than hard-coding values in my filter condition,  How can I make this condition into procedure way.

Thanks in advance!

 

Existing condition:

and a.dlvr_dt between current_date - 365 and current_date

Output should be:

and a.dlvr_dt between current_date – CALL PROCEDURE(example)

and current_date

Mahesh