DB2 Stored Procedure IF statement

Question

I am currently experimenting with stored procedures and I am try to implement a simple IF/ELSE statement. I am using DB2 and I am trying to select all records if the procedure parameter is null and if the parameter is not null, query the database.

My stored procedure code is as follows:

    DROP PROCEDURE LWILSON.IFQUERY@
CREATE PROCEDURE LWILSON.IFQUERY
(
    IN p_type VARCHAR(15)
)

DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c_result CURSOR WITH RETURN FOR
IF p_type IS NULL
THEN
SELECT * FROM LWILSON."ANIMALS";
OPEN c_result;
ELSE 
SELECT ID,TYPE,NAME,WEIGHT, AGE FROM LWILSON."ANIMALS" AS ANIMALRESULTS WHERE ANIMALRESULTS.type = p_type;
OPEN c_result;
END IF;
END@

(I am using the @ symbol for the command separator). The error message I receive when trying to execute the procedure is as follows...

Any help is appreciated. Thanks.

Solution 1

You can do that by preparing the statement to execute. You do not need two cursors in this case:

CREATE OR REPLACE PROCEDURE LWILSON.IFQUERY (
    IN p_type VARCHAR(15)
)
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
 BEGIN
  DECLARE STMT VARCHAR(120);
  DECLARE c_result CURSOR
    WITH RETURN FOR RES_SET;

  IF (p_type IS NULL) THEN
   SET STMT = "SELECT * FROM LWILSON.ANIMALS";
  ELSE 
   SET STMT = "SELECT ID, TYPE, NAME, WEIGHT, AGE "
     || "FROM LWILSON.ANIMALS AS ANIMALRESULTS "
     || "WHERE ANIMALRESULTS.type = " || p_type;
  END IF;
  PREPARE RES_SET FROM STMT
  OPEN c_result;
 END@

Solution 2

Best option I found was creating one cursor for each condition and opening the one you need.

DECLARE cursor1 CURSOR
    WITH RETURN FOR SELECT * FROM LWILSON.ANIMALS;
DECLARE cursor2 CURSOR
    WITH RETURN FOR SELECT * FROM LWILSON.ANIMALS AS ANIMALRESULTS
    WHERE ANIMALRESULTS.type = p_type;

IF (p_type IS NULL) THEN
    OPEN cursor1;
ELSE 
    OPEN cursor2;     
END IF;

Comments

[Rahul]

Don't think you can use if .. else with Cursor definition like that.

[mustaccio]

You need to declare two cursors and open one or the other.

[LiamWilson94]

I tried declaring another cursor below the stated one. I then opened the respective cursor and it still does not work. Any other suggestions? Thanks.

[LiamWilson94]

Thank you, this seems to make more sense. However, I get the following error. DB2 SQL Error: SQLCODE= -104, SQLSTATE=42601, SQLERRMC= p_type,ON.IF.QUERY(IN;,,DRIVER=3.57.110

[LiamWilson94]

The fix was adding escape characters around the parameter being entered.... In DB2 to get a single quote within a string you need to use double quotes ''

[AngocA]

You did not specify the DB2 version. DB2 for LUW support that since version 9.7: www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/…