Dynamic SQL and SELECT INTO

Database
Enthusiast

Dynamic SQL and SELECT INTO

Can I use dynamic SQL and have a SELECT INTO a local variable? I have a statement in a stored procedure that is like

CALL DBC.SYSEXECSQL("SELECT COUNT(*) INTO ROW_CNT FROM MYTABLE WHERE ...");

I am getting the error 5568:SQL Statement is not supported in a stored procedure. It appears the combination of SYSEXECSQL and the INTO clause do not work together in a stored procedure. The only way I can think of doing this is with a volatile table. Am I missing something?

R

Below is the stored proc that I am trying to constuct. I removes all of the spaces in a given character column

REPLACE PROCEDURE SUNPROC.REMOVE_SPACES(IN DB_NAME VARCHAR(30), IN TBL_NAME VARCHAR(30), IN COL_NAME VARCHAR(30))
BEGIN
DECLARE COL_LEN, ROW_CNT INTEGER;
DECLARE SQLSTMT1, SQLSTMT2 VARCHAR(750);

SET SQLSTMT1 = 'UPDATE '||DB_NAME||'.'||TBL_NAME||' ' ||
'SET '||COL_NAME||'=TRIM(SUBSTR('||COL_NAME||',1,INDEX('||COL_NAME||','' '')))||TRIM(SUBSTR('||COL_NAME||',INDEX('||COL_NAME||','' ''))) ' ||
'WHERE INDEX(TRIM('||COL_NAME||'),'' '') > 0';
SET SQLSTMT2 = 'SELECT COUNT(*) INTO ROW_CNT FROM '||DB_NAME||'.'||TBL_NAME||' WHERE INDEX(TRIM('||COL_NAME||'),'' '') > 0';

-- MAKE SURE THAT THE COLUMN IS A CHARACTER BASED COLUMN
SELECT COLUMNLENGTH INTO :COL_LEN
FROM DBC.COLUMNS
WHERE DATABASENAME=B_NAME
AND TABLENAME=:TBL_NAME
AND COLUMNNAME=:COL_NAME
AND SUBSTR(COLUMNTYPE,1,1) ='C';

IF (ACTIVITY_COUNT=1) THEN
GUTS: BEGIN

CALL DBC.SYSEXECSQL(SQLSTMT2); -- THIS SHOULD SET THE ROW_CNT LOCAL VARIABLE
WHILE (ROW_CNT > 0)
DO
CALL DBC.SYSEXECSQL(SQLSTMT1); -- THIS REMOVES THE FIRST SPACE IN THE COLUMN VALUE
CALL DBC.SYSEXECSQL(SQLSTMT2); -- THIS SHOULD SET THE ROW_CNT LOCAL VARIABLE (CHECK FOR COLUMN VALUES THAT HAVE SPACES)
END WHILE;
END GUTS;
END IF;
END
2 REPLIES
Senior Apprentice

Re: Dynamic SQL and SELECT INTO

Hi Ron,
you're right SELECT INTO is not supported for Dynamic SQL and a volatile table would be a solution for that.

But if you're just trying to remove all blanks, this would be much easier using one of the existing UDFs: OREPLACE, which mimics Oracle's Replace function.

If those UDFs are not installed on your system, then there's still no need for that SELECT INTO:
Don't count the number of rows to be updated followed by that update, because this runs two Full Table Scans on that table.
Just run the update and check for ACTIVITY_COUNT, which holds the number of updated rows. Repeat that until it's zero.

Dieter
Enthusiast

Re: Dynamic SQL and SELECT INTO

Hi,

I need to execute the below query in teradata. Is there any way to achieve this?

 SET l_sql = 'SELECT max('||l_rid_col||'), MAX(mentx_cid) INTO c2, c3 FROM '||p_database||'.'||p_table;