Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Unanswered: Errors in SQL Proc when initializing variable with SELECT INTO clause

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •