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.

Don't think you can use if .. else with Cursor definition like that. — Rahul
– Rahul, Commented Oct 14, 2014 at 12:01
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
– LiamWilson94, Commented Oct 14, 2014 at 13:18

Peter Schuetze · Accepted Answer · 2014-10-14 14:39:50Z

3

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@

edited Oct 14, 2014 at 14:39

Peter Schuetze

16.3k4 gold badges47 silver badges59 bronze badges

answered Oct 14, 2014 at 13:27

AngocA

7,7046 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LiamWilson94 Over a year ago

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 Over a year ago

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 Over a year ago

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/…

Ivan · Accepted Answer · 2018-04-12 18:18:45Z

1

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;

answered Apr 12, 2018 at 18:18

Ivan

316 bronze badges

Collectives™ on Stack Overflow

DB2 Stored Procedure IF statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related