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

4 REPLIES
Junior Supporter

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

Hi Raja M,

I am not still not clear about what you want to achieve through this procedure.

But as far as the error is concerned set my_sql ='select count(*) into v_COUNT from t3_rita.'||v_TABLE_NAME||';';  will not work in SP .i.e. you cant assign a variable, a value in a dynamic statement. I have modified that part in the below and handled the error. Other part i have not tested. Please check the below 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 ='CREATE MULTISET VOLATILE TABLE VT_COUNT AS
(
select count(*) AS CNT from MDSP_DATAMART_T.'||v_TABLE_NAME||'
)
WITH DATA
ON COMMIT PRESERVE ROWS;';
CALL DBC.SYSEXECSQL(my_sql);
SELECT CNT INTO v_COUNT FROM DBC.VT_COUNT;
DROP TABLE DBC.VT_COUNT;
INSERT INTO t3_rita. testing_r (v_TABLE_NAME ,v_COLUMN_NM,v_COUNT);
END LOOP L1;
CLOSE PROFILE_SAMPLE;
END;

Thanks,

Rohan Sawant

Fan

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

Thanks a lot Rohan:). I am able to do it by using Cursor. Also I have implemented your methodology for my another requirment.

Thanks and Regards,

Raja M

Teradata Employee

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

I am still getting the error. 

CALL Failed. 7689: PROC_POPULATE_TEST:Invalid dynamic SQL statement.

Below is my Stored Procedure:

REPLACE PROCEDURE DT_SDMT.PROC_POPULATE_TEST( )

BEGIN

DECLARE UPD_STRING VARCHAR(6000);

SET UPD_STRING = 'select * from DD_SDMT.AccountCode_Dim;';

Execute Immediate UPD_STRING;

END;

CALL DT_SDMT.PROC_POPULATE_TEST();

Anyone know to fix this issue.

Thanks.

Teradata Employee

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

SELECT is not valid for EXECUTE IMMEDIATE. If you want to build a SELECT statement dynamically, use a dynamic cursor

DECLARE C1 CURSOR FOR S1;

PREPARE S1 FROM UPD_STRING;

OPEN C1;

If the SELECT statement is always the same, you probably don't need the dynamic / PREPARE form; a static cursor would do.