Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Temporary tables in stored procedures

    Hi,

    I would like to call my sproc again and again without having to drop the temporary table that I'm using. I've declared it in my sproc with the DECLARE GLOBAL TEMPORARY TABLE statement... How can I specify IF NOT EXISTS, then create this table?

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use the WITH REPLACE option when declaring the GTT.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A global temp table is only available during the existence of the thread

    Why do you care?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you call the procedure twice in the same connections (SQL session), then you have to care, of course, because the 2nd attempt on the DECLARE statement will fail.

    What I've been doing sometimes is to have a separate procedure for the DECLARE. In the regular procedure, I do:
    Code:
    IF 1 = 0 THEN
       DECLARE ...
    END IF;
    That makes the SQL compiler happy who has to know about the DGTTs structure.

    Another alternative is to capture the error code with a condition handler.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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