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?
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
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.