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 SQLTXT1='DELETE FROM '||DBNAME||'.TABLE';
SET SQLTXT2='INSERT INTO '||DBNAME||'.TABLE SELECT * FROM '||DBNAME||.TABLE1''
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1 ERRMSG = MESSAGE_TEXT;
IF COUNT=2 OR SQL_ERR_CDE<>0
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.
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.