How to write if logic in store procedure?

grofaty · #1 (**permalink**) 09-14-07, 04:22

Hi,
on db2 v8.2 FP9 on Windows XP I would like to write a store procedure which will declare cursor according to input parameter using if logic.

Sample:

Code:

CREATE PROCEDURE ADMIN.TEST (
     IN InParameter CHAR(1)    )

P1: BEGIN

IF InParameter = '1' THEN
     DECLARE cursor1 CURSOR WITH RETURN FOR SELECT 'A' FROM SYSIBM.SYSDUMMY1;
ELSE
     DECLARE cursor1 CURSOR WITH RETURN FOR SELECT 'B' FROM SYSIBM.SYSDUMMY1;
END IF;     
  
  OPEN cursor1;
END P1    
@

Note: Select statements in above sample are simplified samples. In my case there are two different select statements.

I get error:
================================================== =====================
C:\aaa>DB2 -TD@ -F A.SQL
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include: "<SQL statement>". LINE NUMBER=7.
SQLSTATE=42601
================================================== =====================

It looks like there is some problem using if and declare cursor. Any idea what is wrong with my procedure?

Thanks,
Grofaty

gvee · #2 (**permalink**) 09-14-07, 04:39

Code:

IF InParameter = '1' BEGIN
  ...
END
ELSE BEGIN
 ...
END

?

grofaty · #3 (**permalink**) 09-14-07, 04:59

Hi,
thanks for help.

I wrote it this way and it works fine:

Code:

CREATE PROCEDURE ADMIN.TEST (
     IN InParameter CHAR(1)    )

P1: BEGIN
     DECLARE cursor1 CURSOR WITH RETURN FOR SELECT 'A' FROM SYSIBM.SYSDUMMY1;
     DECLARE cursor2 CURSOR WITH RETURN FOR SELECT 'B' FROM SYSIBM.SYSDUMMY1;

     IF InParameter = '1' THEN  
       OPEN cursor1;
     ELSE
       OPEN cursor2;
     END IF;       

END P1    
@

Thanks,
Grofaty

stolze · #4 (**permalink**) 09-15-07, 09:39

That is fine.

DECLARE statements (for variables, cursors, and condition handlers) must come first in a BEGIN ... END block (compound statement). So either you nest a new compound statement in the branches, or you do what you did.

Marcus_A · #5 (**permalink**) 09-15-07, 10:52

Quote:

Originally Posted by grofaty

Hi,
thanks for help.

I wrote it this way and it works fine:

Code:

CREATE PROCEDURE ADMIN.TEST (
     IN InParameter CHAR(1)    )

P1: BEGIN
     DECLARE cursor1 CURSOR WITH RETURN FOR SELECT 'A' FROM SYSIBM.SYSDUMMY1;
     DECLARE cursor2 CURSOR WITH RETURN FOR SELECT 'B' FROM SYSIBM.SYSDUMMY1;

     IF InParameter = '1' THEN  
       OPEN cursor1;
     ELSE
       OPEN cursor2;
     END IF;       

END P1    
@

Thanks,
Grofaty

This is fine becasue a DECLARE CURSOR does not execute any procedural code until it is opened.

In fact, when using programming languages like COBOL, the DECLARE CURSOR is usually included in the WORKING STORAGE SECTION and not in the PROCEDURE DIVISION.

Internet Services

Web Development

Digital Marketing

Consumer Tech