Wsrtjtyk

I'm having a hard time producing the correct results from my stored procedure. I'm using a db2 database and I have 3 input parameters division, department, project. My call statement looks like this.

CALL schema.stored_procedure ('IT', 'MARKETING', 'ONLINE FULFILLMENT')

I need to produce results that will display the row of data when the third parameter is specified or has a value for the Project Name (as from the example above 'Online_fulfillment') and to display all the results when the third parameter has a value 'ALL' for Project Name (per the example below 'ALL').

CALL schema.stored_procedure ('IT', 'MARKETING', 'ALL')

My query below currently is returning just the column header names with no results and I'm having trouble debugging it. Here is my current stored procedure.

CREATE PROCEDURE schema.stored_procedure

  (IN in_DIVISION_NAME VARCHAR(200)

  ,IN in_DEPARTMENT_NAME VARCHAR(20)

  ,IN in_PROJECT_NAME VARCHAR(400)

  )

  DYNAMIC RESULT SETS 1

 BEGIN

  IF (in_PROJECT_NAME = 'ALL') THEN

   BEGIN

    DECLARE GLOBAL TEMPORARY TABLE TEMP_DW_1

      (DIM_PROJECT_ID INT 

      ,PROJECT_NAME VARCHAR (400) 

      ,DIM_DEPARTMENT_ID INT  

      ,DEPARTMENT_NAME VARCHAR(100) 

      ,DIVISION_NAME VARCHAR(100)  

      ) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;

   END;



    INSERT INTO SESSION.TEMP_DW_1 (DIM_PROJECT_ID, PROJECT_NAME, DIM_DEPARTMENT_ID,

      DEPARTMENT_NAME,DIVISION_NAME)

             SELECT DISTINCT DJ.DIM_PROJECT_ID

            ,PROJECT_NAME

            ,DIM_DEPARTMENT_ID

            ,DEPARTMENT_NAME

            ,DIVISION_NAME

                 FROM SCHEMA.FACT_TABLE 

                 WHERE DEPARTMENT_NAME =  in_DEPARTMENT_NAME

                 AND DIVISION_NAME =  in_DIVISION_NAME; 



          BEGIN

        DECLARE  exitCursor CURSOR WITH RETURN FOR

        SELECT * 

        FROM SESSION.TEMP_DW_1;



        OPEN exitCursor;



           END;

 END 

EXPECTED RESULTS:
CALL schema.stored_procedure ('IT', 'MARKETING', 'ONLINE FULFILLMENT')

EXPECTED RESULTS:
CALL schema.stored_procedure ('IT', 'MARKETING', 'ALL')

I believe I have solved this by adding an additional IF statement setting the in_PROJECT_NAME <> 'ALL' and adding an additional filter to the second query that sets the PROJECT_NAME = in_PROJECT_NAME. Could be an easier way to solve this but it works:

IF (in_PROJECT_NAME <> 'ALL') THEN

 BEGIN

  DECLARE GLOBAL TEMPORARY TABLE TEMP_DW_1

    (DIM_PROJECT_ID INT 

    ,PROJECT_NAME VARCHAR (400) 

    ,DIM_DEPARTMENT_ID INT  

    ,DEPARTMENT_NAME VARCHAR(100) 

    ,DIVISION_NAME VARCHAR(100)  

    ) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;

 END;



 INSERT INTO SESSION.TEMP_DW_1 (DIM_PROJECT_ID, PROJECT_NAME, DIM_DEPARTMENT_ID,

   DEPARTMENT_NAME ,DIVISION_NAME)

   SELECT DISTINCT DJ.DIM_PROJECT_ID

     ,PROJECT_NAME

     ,DIM_DEPARTMENT_ID

     ,DEPARTMENT_NAME

     ,DIVISION_NAME

     FROM SCHEMA.FACT_TABLE 

     WHERE DEPARTMENT_NAME = in_DEPARTMENT_NAME

     AND DIVISION_NAME =  in_DIVISION_NAME; 

     AND PROJECT_NAME = in_PROJECT_NAME