Topic
  • 2 replies
  • Latest Post - ‏2013-09-23T21:42:40Z by Junius
Junius
Junius
17 Posts

Pinned topic Stored Procedure with resultset and IF stmt

‏2013-09-23T15:04:51Z |

How do I structure a Stored Procedure to return a resultset AND use calculated fields in the Select statement?   In otherwords, I need to calculate several fields before running the SELECT statement, since the SELECT statement needs these values in the WHERE clause.

Below is a sample we've written that does not work, apparently because we have an IF statement before the DECLARE CURSOR.  I have shorted the SELECT statement and the IF statement for the purposes of this post.

CREATE PROCEDURE VND_SUM_DATA(
IN YR1 INTEGER , IN YR2 INTEGER , IN YR3 INTEGER , IN EP INTEGER )
LANGUAGE SQL
READS SQL DATA
SPECIFIC VNDSUMDTA
PROGRAM TYPE MAIN
DYNAMIC RESULT SETS 1


BEGIN

-- Declare Variables
Declare cur_period integer;
Declare cur_year integer;
Declare scd_year integer;
Declare thd_year integer;


IF YR1 = 0 and YR2 = 0 and YR3 = 0 and EP = 0

  -- statements to set appropriate dates for selection

End IF;


Declare CI cursor with return for

   Select vendor, longsku, shortsku, dept, class, subclass, skustat,year
    from VNDITMSUM
      join SLSDTA on shortsku = ssku and year = yr
    where scd_year = year ;


Open C1;

END;

 

 

Thank you,

Jay

 

  • krmilligan
    krmilligan
    450 Posts

    Re: Stored Procedure with resultset and IF stmt

    ‏2013-09-23T19:13:41Z  

    Just move the Declare Cursor statement after the variable declarations.  The cursor declaration will still use the updated variables even though it sits before the logic that sets the variables.

  • Junius
    Junius
    17 Posts

    Re: Stored Procedure with resultset and IF stmt

    ‏2013-09-23T21:42:40Z  

    Just move the Declare Cursor statement after the variable declarations.  The cursor declaration will still use the updated variables even though it sits before the logic that sets the variables.

    Thank you Kent, we'll try it that way.

    Jay