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!!

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.

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.