how to pass parameter in Stored procedure

[ketandba]

Hi,
I am newbie to right stored procedure.
In my Sp i have 3 cursors. i have to pass the value of first cursor to second & third cursor.

Here is my sp.

CREATE PROCEDURE DB2ADMIN.GETOLDVEHICLE_AMF(IN carrAcctNum integer, IN fleetNum smallint, IN fleetMonth smallint, IN fleetCCYY smallint, IN suppNum smallint, IN vehId integer, OUT OUT_SQLCODE int )
       DYNAMIC RESULT SETS 3
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
        DECLARE v_app_msg 		VARCHAR(200) DEFAULT '';
        DECLARE v_db2_msg 		VARCHAR(200) DEFAULT '';
        DECLARE v_error    	        SMALLINT DEFAULT 0;
--
        DECLARE v_vehId    	          INTEGER;
        DECLARE v_supp_nbr            smallint;
        DECLARE v_service_type_i      CHAR(3);
        DECLARE v_weight_group_nbr    INTEGER;
        DECLARE v_veh_supp_eff_d      date;
        DECLARE v_veh_service_ts      TIMESTAMP;
        DECLARE v_colorado_f          CHAR(1);
        DECLARE v_colorado_trl_f      CHAR(1);
        DECLARE v_utah_special_trk_i  CHAR(1);


        DECLARE vehSupInfoCursor CURSOR WITH RETURN FOR

        SELECT VEH_ID,SUPP_NBR,SERVICE_TYPE_I,WEIGHT_GROUP_NBR,VEH_SUPP_EFF_D,
               VEH_SERVICE_TS ,COLORADO_F,COLORADO_TRL_F,UTAH_SPECIAL_TRK_I

        FROM MC.IRP_VEHICLE_SUPP

        WHERE  CARRIER_ACCT_NBR = carrAcctNum AND
               FLT_NBR          = fleetNum AND
               FLT_EXP_CCYY     = fleetCCYY AND
               FLT_EXP_MM       = fleetMonth AND
               VEH_ID        = vehId AND
               SERVICE_TYPE_I    IN ('ADV','ADX','AMF','REN',
                          'STR','WGT','ADJ','CHC',
                          'FTF','NEW','NFL','CNV') AND

                SUPP_NBR =
                            (SELECT MAX (SUPP_NBR)
                             FROM   MC.IRP_VEHICLE_SUPP
                             WHERE  CARRIER_ACCT_NBR =  carrAcctNum
                               AND  FLT_NBR =    fleetNum
                               AND  FLT_EXP_CCYY =  fleetCCYY
                               AND  FLT_EXP_MM =   fleetMonth
                               AND  VEH_ID =  vehId
                               AND  SUPP_NBR < suppNum
                               AND  SERVICE_TYPE_I IN ('ADV','ADX','AMF','REN',
                                                        'STR','WGT','ADJ','CHC',
                                                         'FTF','NEW','NFL','CNV'));



	    DECLARE vehicleInfoCursor CURSOR WITH RETURN FOR

        SELECT  VIN_I,UNLADEN_WEIGHT_Q,FUEL_TYPE_I,MODEL_CCYY,AXLES_Q,BODY_STYLE_I,SEATS_Q,COMBINED_AXLES_Q
                FROM MC.IRP_VEHICLE
                WHERE VEH_ID=[COLOR=red]v_vehId[/color]     AND
                      VEH_SERVICE_TS=[COLOR=red] v_veh_service_ts[/color];

        DECLARE vehRegInfoCursor CURSOR WITH RETURN FOR

        SELECT PURCHASE_M,PURCHASE_D,FACTORY_M,VEH_ID,GROSS_WEIGHT_Q
                FROM MC.IRP_REGISTRATION
                WHERE  CARRIER_ACCT_NBR = carrAcctNum AND
                       FLT_NBR          = fleetNum AND
                       FLT_EXP_CCYY     = fleetCCYY AND
                       FLT_EXP_MM       = fleetMonth AND
                       SUPP_NBR <= suppNum AND
                       VEH_ID = [COLOR=red]v_vehId [/color]   AND
                       SUPP_NBR IN (SELECT MAX(SUPP_NBR)
                                    FROM MC.IRP_REGISTRATION
                                    WHERE CARRIER_ACCT_NBR = carrAcctNum AND
                                          FLT_NBR          = fleetNum AND
                                          FLT_EXP_CCYY     = fleetCCYY AND
                                          FLT_EXP_MM       = fleetMonth AND
                                          SUPP_NBR         <= suppNum AND
                                          VEH_ID = [COLOR=red]v_vehId[/color]);


--  DECLARE oldSuppEffDt CURSOR WITH RETURN FOR

--       SELECT SUPP_EFF_D  FROM MC.IRP_SUPPLEMENT
--                        WHERE CARRIER_ACCT_NBR = carrAcctNum
--                            AND FLT_NBR = fleetNum
--                            AND FLT_EXP_CCYY =  fleetCCYY
--                            AND FLT_EXP_MM =   fleetMonth
--                            AND SUPP_NBR =  v_supp_nbr;


        -- Error Handling
        DECLARE CONTINUE HANDLER FOR NOT FOUND
        BEGIN NOT ATOMIC
                SET v_error = 1;
        END;
        DECLARE CONTINUE HANDLER FOR SQLWARNING
        BEGIN NOT ATOMIC
                SET v_error = 2;
        END;
        -- Open the cursor which contains the Result Set.
        OPEN vehSupInfoCursor;
        FETCH FROM  vehSupInfoCursor  INTO
               v_vehId,v_supp_nbr,v_service_type_i,v_weight_group_nbr,v_veh_supp_eff_d,
               v_veh_service_ts, v_colorado_f, v_colorado_trl_f, v_utah_special_trk_i;
      --  close vehSupInfoCursor ;
        OPEN vehicleInfoCursor; -- using v_vehId,v_veh_service_ts;
        OPEN vehRegInfoCursor; --  using v_vehId;
--        OPEN oldSuppEffDt using v_supp_nbr;
END P1

******
i have to pass the veh_id & veh_service_ts in second & thrid cursor.

pl. help me out.

Thanks in advanced.