I have the following query:
CREATE PROCEDURE MYPROC(
IN @TABLES VARCHAR(500))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC MYPROC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN
--declare variables
DECLARE @TABLE VARCHAR ( 100 ) ;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT * FROM TABLE(split(@TABLES, ';')) AS FUNC;
-- create temp table
DECLARE GLOBAL TEMPORARY TABLE SESSION . KEYS
(
INMATEID INTEGER NOT NULL ,
ROWID INT GENERATED ALWAYS AS IDENTITY ( START WITH 1 , INCREMENT BY 1 )
)
WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ;
IF ( @INMATEID IS NOT NULL ) THEN
INSERT INTO SESSION . KEYS ( INMATEID ) VALUES ( @INMATEID ) ;
ELSE
INSERT INTO SESSION . KEYS ( INMATEID )
SELECT MyTable.ID
FROM MYTABLE;
END IF ;
-- select tables
OPEN C1;
FETCH C1 INTO @TABLE;
WHILE (SQLCODE = 0) DO
BEGIN
If (@TABLE = 'INMATES') THEN
SELECT INMATES.* FROM ATABLE; <-- THIS IS THE PROBLEM SPOT!
END IF;
FETCH C1 INTO @TABLE;
END;
END WHILE;
CLOSE C1;
END;
The problem is marked in the above procedure. I want to loop through the passed in 'tables' and SELECT data from the tables that are passed in. The problem is that I cannot do a SELECT statement within my while loop and if statement. Are there any syntax problems that you see or is it something else entirely?
When I try to CREATE the procedure I get this error message:
SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, or trigger STSSP_JAILIF_GETDATA in PJGVDGD1 not created.
I've narrowed it down to that it's definitely related to my 'SELECT INMATES.*' line in the IF Statement.
Best Answer
The DB2 Docs suggest to me that returning a dynamic result set is done via a
cursor
with aWITH RETURN
clause rather than a simpleselect
statement.