DB2 stored procedure w/ parameters

Question

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') enter image description here EXPECTED RESULTS: CALL schema.stored_procedure ('IT', 'MARKETING', 'ALL')

Two things: 1) Why do you dump the results into a temp table, then open a new cursor and return all rows from the temp table (as opposed to just opening a cursor over the initial `SELECT`)? 2) This procedure actually appears amenable to being created as a view, which would likely be better able to use the optimizer in cases where you're joining to the results. Obviously, you couldn't supply parameters for the view, but it'd be trivial to supply them as a join condition/as part of a `WHERE` clause. — Clockwork-Muse, May 20 '14 at 10:08

score 0 · Answer 1 · edited May 20 '14 at 19:16

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

Your solution seems good, however if you have many cases, and each time you need to copy the same select, then the best is to use a dynamic query (prepare, execute). — AngocA, May 20 '14 at 06:20

DB2 stored procedure w/ parameters

1 Answers1