Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Question Unanswered: Returning more than one ResultSet from Stored Procedure

    All,
    It works fine when I return one ResultSet from a Procedure, but now when I return more than ResultSet. Below is my procedure:

    create procedure show_employee_department()
    dynamic result sets 2
    begin
    declare c_emp cursor with return for select * from employee;
    open c_emp;

    delcare c_dept cursor with return for select * from department;
    open c_dept;
    end

    I get this error:
    SQL0104N An unexpected token "delcare" was found following "loyee;
    open c_emp;

    ". Expected tokens may include: "DECLARE

    My Statement Termination character is '@'.

    What am I missing here? Thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at the syntax diagram for creating a store procedure. The DECLARE CURSOR statements must all be at the beginning.

    Try this:

    Code:
    create procedure show_employee_department()
    dynamic result sets 2
    begin
    declare c_emp cursor with return for select * from employee;
    delcare c_dept cursor with return for select * from department;
    
    
    open c_emp;
    
    open c_dept;
    end
    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sequence of statements in a compound SQL statement are fixed.
    For example, all DECLARE-CURSOR-statements should be placed before SQL-procedure-statements(including open cussor statement).

    Please see more detaile in IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  4. #4
    Join Date
    Mar 2010
    Posts
    7
    Thanks guys.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by sunny8107 View Post
    All,
    It works fine when I return one ResultSet from a Procedure, but now when I return more than ResultSet. Below is my procedure:

    create procedure show_employee_department()
    dynamic result sets 2
    begin
    declare c_emp cursor with return for select * from employee;
    open c_emp;

    delcare c_dept cursor with return for select * from department;
    open c_dept;
    end

    I get this error:
    SQL0104N An unexpected token "delcare" was found following "loyee;
    open c_emp;

    ". Expected tokens may include: "DECLARE

    My Statement Termination character is '@'.

    What am I missing here? Thanks in advance!

    Did anyone notice spelling mistake above ??


    regards

Posting Permissions

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