Post reply

Several Local Temp Table questions

  • September 10, 2003 at 1:39 pm

    #101660

    I am having trouble understanding several issues regarding Local Temporary Tables. Perhaps you can help me understand some things:

    1) What is the duration of a (#) temp table? I thought it was for as long as the Connection used to create it was open, but it seems that a temp table created by a stored procedure is always dropped at the completion of the stored procedure. (If I created a temp table directly from VB code it DOES persist as long as my connection is open.)

    2) If each stored procedure drops all local temp tables created during execution, is there any way to bypass this so that other stored procedures can use them?

    Here's what I'd like to do: From VB I'm trying to execute a Stored procedure that creates a temp table, then refer to that temp table after the stored procedure completes. I'd like to use the temp table (which pulls data from many tables and does some basic aggregation) as a source for several aggregate recordsets. I want to create the other recordsets using either stored procedures, or the .Execute method of the Active Connection object in VB.

    Specifics: I'm running SQL 7.0 sp3, Access 2000 adp, VBA controls the interface.

    Thanks In advance.

  • September 10, 2003 at 1:54 pm

    #473535

    Look up CREATE TABLE in BOL (and read under TEMPORARY TABLES) for the full explanation. I cut this excerpt out:

    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

    I think that answers your first question.

    To answer your second question- at first glace it appears you have two options. You could either create the table in the calling stored procedure, since it will be available to any stored procedure that procedure will call. OR you could use a physical table


    -Ken

  • September 10, 2003 at 2:00 pm

    #473536

    THANK YOU! I can't even count how many times I've read that BOL subject and yet managed to miss that particular statement...ARRGH! At least I can stop beating my head on the desk now!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply