I am trying to build this DB2 stored procedure :




DELETE FROM PROJVAR_XREF
WHERE VARIABLE_ID=v_VariableId;

UPDATE MODEL_MDI
SET ROOTVAR = NULL
WHERE ROOTVAR = v_VariableId;

CALL PROC_DELETE_VARIABLE(v_VariableId);

END IF;



END LOOP fetch_loop;

CLOSE c_ProjectVariable1;


DELETE FROM MODEL_MDI WHERE MODEL_MDI.PROJECT_ID = v_ProjectId ;


END p1


but i was constantly getting an error :
SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=20. SQLSTATE=42601

i think there is some problem with the cursor declaration i am unable to resolve , Hope you can get a solution to this .
Please help its very urgent

Your DECKARE CURSOR Statment should be at the beginning of the procedure (or more precisely, at the beginning of a compound statement before any 'executable' SQL Statement ...

Ex:

BEGIN
DECLARE v_salary DOUBLE;
DECLARE v_years SMALLINT;
DECLARE v_id SMALLINT;
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

-- CAST salary as DOUBLE because SQL procedures do not support DECIMAL
DECLARE C1 CURSOR FOR
SELECT id, CAST(salary AS DOUBLE), years
FROM staff;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

SET v_id=100 ;
OPEN C1;

etc ...

If the declare currsor is after the SET v_id=100 statement, then you will get an error

HTH

Sathyaram

Here is my complete code can u make corrections :

CREATE PROCEDURE PROC_DELETE_PROJECT (IN P_PROJECT_NAME VARCHAR(35))
LANGUAGE SQL

p1: BEGIN

DECLARE v_ProjectId INT DEFAULT 0;
DECLARE v_VariableId INT DEFAULT 0;
DECLARE v_DataConnectionId INT DEFAULT 0;
DECLARE v_Count INT DEFAULT 0;
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE SQLSTATE CHAR(5);

DECLARE not_found
CONDITION for SQLSTATE '02000';

DECLARE CONTINUE HANDLER for not_found
SET at_end = 1;

DECLARE c_ProjectVariables CURSOR FOR
select projvar_xref.variable_id
from projvar_xref
where projvar_xref.project_id= v_ProjectId;

SELECT PROJECT_MDI.ID
INTO v_ProjectId
FROM PROJECT_MDI
WHERE PROJECT_MDI.NAME = P_PROJECT_NAME ;

SELECT DATACONNECTION_ID
INTO v_DataConnectionId
FROM DATAPROJ_XREF
WHERE PROJECT_ID=v_ProjectId;



DELETE FROM DATAPROJ_XREF WHERE DATAPROJ_XREF.PROJECT_ID = v_ProjectId;
DELETE FROM DATACONNECTION_MDI WHERE ID=v_DataConnectionId;
DELETE FROM CUPROJ_XREF WHERE CUPROJ_XREF.PROJECT_ID = v_ProjectId;

OPEN c_ProjectVariables;
fetch_loop:
LOOP

FETCH c_ProjectVariables INTO v_VariableId;
IF at_end <> 0 THEN LEAVE fetch_loop;
END IF;

SELECT COUNT(*) into v_Count
FROM PROJVAR_XREF
WHERE variable_id=v_VariableId
AND project_id <> v_ProjectId;

IF v_Count > 0 THEN

DELETE FROM PROJVAR_XREF
WHERE VARIABLE_ID=v_VariableId
AND PROJECT_ID=v_ProjectId;

UPDATE MODEL_MDI
SET ROOTVAR = NULL
WHERE ROOTVAR = v_VariableId;


ELSE



DELETE FROM PROJVAR_XREF
WHERE VARIABLE_ID=v_VariableId;

UPDATE MODEL_MDI
SET ROOTVAR = NULL
WHERE ROOTVAR = v_VariableId;

CALL PROC_DELETE_VARIABLE(v_VariableId);

END IF;



END LOOP fetch_loop;

CLOSE c_ProjectVariables;


DELETE FROM MODEL_MDI WHERE MODEL_MDI.PROJECT_ID = v_ProjectId ;


END p1

put your cursor declaration before declare handler