I have the following SQL stored proc that won't compile. It will compile if I don't try and initialize the fiscalyr variable via the SELECT ... INTO fiscalyr statement.

The error is:

SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=21. SQLSTATE=42601

The proc is:

CREATE PROCEDURE ADMIN.Proc1 (IN wk_end_date timestamp)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE fiscalyr CHAR(7);

SELECT MAX(fiscal_year) INTO fiscalyr
FROM ADMIN.ACTIVITY_DATES
WHERE week_ending_date = wk_end_date;

DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR

WITH CONTACTS AS (
SELECT loc_id, CONCAT (CONCAT (fname, ' '), lname) AS full_name
FROM ADMIN.LOC_CONTACT lc , ADMIN.CONTACT c
WHERE title_code = 'SAR' AND lc.contact_id = c.contact_id
AND fiscal_year = fiscalyr)

SELECT * FROM CONTACTS;

OPEN c1;

END P1

Anybody have any suggestions or know what the problem is?

Please note that everything after the DECLARE CURSOR statement works correctly if I hard-code the fiscalyr variable with a value. The stored proc compiles and returns the correct data when called from the client app. So the problem must be above in the SELECT INTO statement.

Thanks

Does this work ?

CREATE PROCEDURE ADMIN.Proc1 (IN wk_end_date timestamp)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE fiscalyr CHAR(7);



DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR

WITH CONTACTS AS (
SELECT loc_id, CONCAT (CONCAT (fname, ' '), lname) AS full_name
FROM ADMIN.LOC_CONTACT lc , ADMIN.CONTACT c
WHERE title_code = 'SAR' AND lc.contact_id = c.contact_id
AND fiscal_year = fiscalyr)

SELECT * FROM CONTACTS;

SELECT MAX(fiscal_year) INTO fiscalyr
FROM ADMIN.ACTIVITY_DATES
WHERE week_ending_date = wk_end_date;

OPEN c1;

END P1