SQL0104N An unexpected token "<cursor declaration>" was found

[ketandba]

hi,
i am getting SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>".

Here is my stored procedure--
<code>
CREATE PROCEDURE DB2ADMIN.GETVEHICLES(IN carrAcctNum integer, IN fleetNum smallint, IN fleetMonth smallint, IN fleetCCYY smallint, IN suppNum smallint,IN startIndex integer, IN endIndex integer, OUT OUT_SQLCODE int )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- 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 start_index SMALLINT DEFAULT 0;
DECLARE end_index SMALLINT DEFAULT 0;

SET start_index = startIndex;
SET end_index = endIndex;

DECLARE vehicleInfoCursor CURSOR WITH RETURN FOR
WITH ALLVEHDATA(RN,VEH_ID,TFR_FRM_FL_NBR,TFR_PLATE_NBR,SRVC_TYPE_IND,WGT_GROUP_NBR,VEH_SUP_EFF_DT,
VIN_ID,UNLADEN_WGT_Q,FUEL_TYPE_IND,MODEL_CCYY,AXLES_Q,BODY_STYLE_IND,SEATS_Q,FACTORY_M)
AS
(
SELECT ROW_NUMBER() OVER() AS RN, A.VEH_ID,A.TFR_FRM_FL_NBR,A.TFR_PLATE_NBR,A.SRVC_TYPE_IND,A.WGT_GROUP_NBR,A.VEH_SUP_EFF_DT,
B.VIN_ID,B.UNLADEN_WGT_Q,B.FUEL_TYPE_IND,B.MODEL_CCYY,B.AXLES_Q,B.BODY_STYLE_IND,B.SEATS_Q,
C.FACTORY_M
FROM DB2ADMIN.MCS_IR_VEH_SUPP A, DB2ADMIN.MCS_IR_VEH B, DB2ADMIN.MCS_IR_REG C WHERE
A.VEH_ID = B.VEH_ID AND
A.VEH_ID = C.VEH_ID AND
A.ACCOUNT_NBR = C.CARRIER_AC_NBR AND
A.FL_NBR = C.FL_NBR AND
A.SUP_NBR = C.SUP_NBR AND
A.FL_EXP_CCYY = C.FL_EXP_CCYY AND
A.FL_EXP_MM = C.FL_EXP_MM AND
A.FL_NBR = fleetNum AND
A.FL_EXP_CCYY = fleetCCYY AND
A.FL_EXP_MM = fleetMonth AND
A.SUP_NBR = suppNum AND
A.ACCOUNT_NBR = carrAcctNum)
SELECT RN, VEH_ID,TFR_FRM_FL_NBR,TFR_PLATE_NBR,SRVC_TYPE_IND,WGT_GROUP_NBR,VEH_SUP_EFF_DT,
VIN_ID,UNLADEN_WGT_Q,FUEL_TYPE_IND,MODEL_CCYY,AXLES_Q,BODY_STYLE_IND,SEATS_Q,FACTORY_M
FROM ALLVEHDATA WHERE RN BETWEEN start_index AND end_index;
-- 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 vehicleInfoCursor;
END P1
</code>

Actually i want the result between start_index & endd_index.
It means if sql returns 500 rows, then i want say betwenn 11 and 20 only.

Pl. replay
Thanks in advanced.

Ketan

 

[ddiamond]

Try moving the
SET start_index = startIndex;
SET end_index = endIndex;

After the cursor definition. The order in which things are declared matters.