Looping in Stored Procedure

Database
Enthusiast

Looping in Stored Procedure

Hi,

 I am new to teradata SPs n trying to write a loop.

 Below is the sample code, I want to assign QUERIES to variables SQLTXT1,SQLTXT2,SQLTXT3 ... and run with SYSEXECSQL. so I can run any no. of queries
 in loop and want to leave loop if SQL_ERR_CODE <> 0 .

 SET Count=0

 SET SQLTXT1='DELETE FROM '||DBNAME||'.TABLE';
 SET SQLTXT2='INSERT INTO '||DBNAME||'.TABLE SELECT * FROM '||DBNAME||.TABLE1''
SET SQLTXT3='QUERY.....'
..
..
..

 L1: LOOP

 Q1: BEGIN

 DECLARE EXIT HANDLER FOR SQLEXCEPTION

 SET COUNT=COUNT+1;
 SET SQLTXT='SQLTXT:COUNT';
 CALL DBC.SYSEXECSQL(:SQLTXT)

 END Q1;

 GET DIAGNOSTICS EXCEPTION 1 ERRMSG = MESSAGE_TEXT;
 
 IF COUNT=2 OR SQL_ERR_CDE<>0
 THEN
 LEAVE L1;
 END IF;
 END LOOP L1;

I am not able to assign a query from SQLTXT1,2,3 ... to SQLTXT variable. I am doing like this to capture log for every query I run in SP.

Please help me on this.

 Best Regards,
 Mahesh
2 REPLIES
Enthusiast

Re: Looping in Stored Procedure

Mahesh,

Try this,

SET COUNT=COUNT+1;

SET SQLTXT='SQLTXT'||COUNT;

CALL DBC.SYSEXECSQL(:SQLTXT)

Thanks,

Dinesh

Teradata Employee

Re: Looping in Stored Procedure

You can't dynamically evaluate a variable name in SPL. If you put the SQL text into a table (with a sequence number column and with appropriate host variable names in the statement text), you could open a cursor, fetch from the table, and pass the text of each row to DBC.SysExecSQL / EXECUTE IMMEDIATE.