Hi,

Is it possible to create a temporary table in a stored procedure so that the
table is scoped local to the procedure, i.e:

Informix:
    CREATE TEMP TABLE MyTable ...

MS SQL:
     CREATE TABLE #MyTable ...

Thanks
Louis van den Berg

Hi,
  It is impossible to use DDL in SP.

Regards,
Yong, Wu

Louis van den Berg <lou...@engineparts.co.za> wrote in message

Louis van den Berg wrote:

Hi,

Maybe something like this could work:

Create a "normal" table with the columns you need + one extra column used as
identifier.

Use a generator in the SP to get a unique identifier for each execution of the
SP.
Storing the identifier in the table makes it possible for the SP to work only
with its own records.

HTH,
Martin

Louis van den Berg wrote:

   What is the purpose of TMP table? Maybe you have to think about FOR SELECT
from existing table/join/view or nested selects instead it?

Louis van den Berg wrote:

If  you can force the user to login once at a time you can do what I did:

I create my temp table just like a normal table,  I pad the tablename
with the user name.
Exampl, I will have :
Create table salestablesysdba (...)

My reason for not using a unique identifier for each connection is that  
I have very little control to the clients machine.
If the clients machine dies, temp Tables are left  there and keeps on
accumulating , before you know it, you already run out of disk space.

I first check if the tables exist then drop it, before creating it.

good luck!
mac