Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    51

    Unanswered: Returning Records from Stored Procedures

    I have to write some complex logic in stored procedures and I see that the 'common table' syntax in db2 sql would be helpful.

    But I don't get how the DECLARE CURSOR RETURN FOR CLIENT and
    OPEN CURSOR syntax requirements are going to work out for this.

    How do I open temporary/common tables in a stored procedure and then get them into a cursor and return the records?

    For me the goal would be to flexibly create a recordset/table on the fly from a range of sources and techniques and then return the version of it that I want to define as the 'final result'.

    So again, how does the OPEN CURSOR syntax work?

    If I just start by trying to create a table like:

    WITH TEMP1 AS
    (SELECT SOME STUFF);

    I immediately wonder what I'm doing.

    Thanks,

    Kimball

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    In order to create, fill, the select and return data from a temporary table, you need to perform a little trick to get around the syntax.

    Create procedure myproc(...)
    ...
    DECLARE GLOBAL TEMPORARY TABLE fred ...

    INSERT INTO SESSION.FRED ...

    ...

    BEGIN
    DECLARE CURSOR1 CURSOR WITH RETURN TO CLIENT FOR SELECT * from SESSION.FRED;

    OPEN CURSOR1;

    END;

    END

    HTH

    Andy

  3. #3
    Join Date
    Aug 2004
    Posts
    51

    That's It!

    So I can see how that would work, and I recognize that I can look up a lot of the details in the docs.

    But I read somewhere you can create joins and such with the global tables.

    So how about this?

    DECLARE GLOBAL TABLE FRED;
    INSERT INTO SESSION.FRED SELECT * FROM tblFriends;

    DECLARE GLOBAL TABLE FRED2 ;
    INSERT INTO SESSION.FRED2 SELECT * FROM tblFoes;

    DECLARE GLOBAL TABLE WARFRED;
    INSERT INTO SESSION.WARFRED SELECT FRIENDS.*, FOES.* FROM SESSION.FRED FRIENDS INNER JOIN SESSION.FRED2 FOES;

    BEGIN
    DECLARE CURSOR WARANDPEACE FOR RETURN TO CLIENT
    OPEN CUSOR WARANDPEACE
    END

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Looks OK to me.

    Andy

  5. #5
    Join Date
    Aug 2004
    Posts
    51

    Well OK then!

    Thanks 10 chars!

Posting Permissions

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