Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
missing semicolon after SELECT INMATES.* FROM ATABLE? –  Jack Douglas Jun 15 '11 at 19:25
    
woops, just forgot it when transferring. thanks Jack I put it back in the code. –  HelpMe Jun 15 '11 at 19:31
    
It would be extremely helpful if you include the version of DB2 you are running and the platform it is running on. –  Ian Bjorhovde Jun 15 '11 at 23:49
add comment

1 Answer

The DB2 Docs suggest to me that returning a dynamic result set is done via a cursor with a WITH RETURN clause rather than a simple select statement.

share|improve this answer
    
I see, so is it not possible to loop through and return multiple SELECTS? –  HelpMe Jun 15 '11 at 19:32
    
The way I read it you can return multiple sets by leaving more than 1 cursor open and setting DYNAMIC RESULT SETS to the maximum number of sets you anticipate returning? –  Jack Douglas Jun 15 '11 at 19:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.