Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Unanswered: Help With Procedure

    I have the following query:

    Code:
    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?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What should happen with the rows returned by the SELECT statement? You don't consume them. Do you want to open a cursor that is to be returned to the caller? Do you want to fetch the results and do something with them? Either way, you'll have to declare a cursor for this query and do something with that cursor.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •