STORED PROCEDURE WITH INPUT PARAMETERS & IF STATMENT 2

[Ann28]

Hello everyone!

REALLY APPRECIATE ALL YOUR HELP!

I need to create a sample procedure with one input parameter.
If the parameter is null than select all from the table, else select the data based on the fund.

Here is the procedure:

CREATE PROCEDURE basys.MemberByFund ( IN in_fund VARCHAR(6) )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR

    Declare p_in_fund VARCHAR(6);
    set p_in_fund = in_fund;
    
        If p_in_fund is Null then
            SELECT SSN FROM  basys.UNITE_HERE_INITIAL;
        ELSE
                SELECT *
                FROM basys.UNITE_HERE_INITIAL
                Where
                  FUND = in_fund
              ORDER BY
              FUND, SSN
            END IF;
                 -- Cursor left open for client application
    OPEN cursor1;
END P1

error:
Create stored procedure returns -104.
basys.MemberByFund: 10: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "Declare p_in_fund VARCHAR" was found following "OR WITH RETURN FOR ". Expected tokens may include: "<values>". LINE NUMBER=10. SQLSTATE=42601

Thanks
Ann

 

[ddiamond]

You need to declare the cursor with a select statement following the for.

Try the following:

P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT SSN FROM basys.UNITE_HERE_INITIAL;

DECLARE cursor2 CURSOR WITH RETURN FOR
SELECT *
FROM basys.UNITE_HERE_INITIAL
Where
FUND = in_fund
ORDER BY
FUND, SSN;

Declare p_in_fund VARCHAR(6);
set p_in_fund = in_fund;

If p_in_fund is Null then
OPEN cursor1;
ELSE
OPEN cursor2;
END IF;
END P1

 

[Mercury2]

Try this.

CREATE PROCEDURE MemberByFund ( IN in_fund VARCHAR(6) )  
    DYNAMIC RESULT SETS 1                                
                                            
Begin                                                    
     Declare p_in_fund VARCHAR(6);                       
     Declare stmt varchar(2000);                         
     Declare C1 CURSOR With return for S1;               
     Set p_in_fund = in_fund;                            
     If p_in_fund is Null then                           
       Set stmt = 'SELECT SSN FROM UNITE_HERE_INITIAL';  
     else                                                
       Set stmt =  'SELECT * FROM UNITE_HERE_INITIAL     
                       WHERE  FUND = p_in_fund   
                         ORDER BY FUND, SSN';  
      end if;                                 
      Prepare S1 from stmt;                   
      Open C1; 
 
      Fetch C1 into ...;                              
 End

I think that it's at least worth giving a try.

 

[Ann28]

THANK YOU ALL FOR YOUR HELP!

 

[Ann28]

Fetch C1 into ...; ??????

 

[Mercury2]

Sorry I made a mistake, my previous posting should have read what follows instead
...
Set stmt = 'SELECT * FROM UNITE_HERE_INITIAL
WHERE FUND = ' CONCAT p_in_fund
CONCAT ' ORDER BY FUND, SSN';

That's the prepare stm that makes the main difference with your procedure.

About the fetch, how will you get the data if you don't read (fetch) them ?