Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write if logic in store procedure?

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    IF InParameter = '1' BEGIN
      ...
    END
    ELSE BEGIN
     ...
    END
    ?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •