Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Return cursor on temp table in stored procedure?

    Hi,

    I have two stored procedures, one that initializes a temp table and another that calls it..

    SPROC1 : TEMP TABLE SPROC has something like this --

    SET tmpStatement = 'DECLARE GLOBAL TEMPORARY TABLE SESSION.tmp_readings LIKE TMPREADINGS NOT LOGGED';

    EXECUTE IMMEDIATE tmpStatement;
    END



    SPROC 2 : SPROC THAT USES TEMP TABLE --

    CALL SPROC1() --- to define the temp table

    DECLARE C1 CURSOR WITH RETURN FOR SELECT * from SESSION.tmp_readings;


    The problem is this -- when I create SPROC2, it complains in the cursor statement above that SESSION.tmp_readings hasn't been defined yet. Is there some keyword I can give the DECLARE GLOBAL TEMPORARY TABLE statement so that it recognizes the table when it comes to the cursor statement? Thanks!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you use dynamic SQL to declare the table, you should use the same to select from it, because you can refer to the table definition only at run time.

  3. #3
    Join Date
    Apr 2009
    Posts
    42
    here is a trick to help pass complie for your sp2. in your sp2, write:
    IF 1 = 2 THEN
    {
    DECLARE TEMP TABLE HERE...
    }

    call your sp1;

    the if statement will help you pass compilation, while in run time, the if statement will not execute.

Posting Permissions

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