Skip to Content
author's profile photo Former Member
Former Member

Store Procedures do not delete temp tables created in that procedure

We have noticed that when we use a temp table in a stored procedure and then execute the procedure, the temp table remains. So if we execute the procedure again, it will fail with a table already exists error.

Are we missing something? Here is what we are trying to do:

CREATE PROCEDURE SessionVariableGetMany

(

SessionId CHAR ( 16 ),

VarNames CHAR ( 2000 ),

CURSOR VARYING OUTPUT

)

BEGIN

Declare @Sql char(2000);

@Sql = 'Select SV_Key, SV_Value Into #tempSVGM From SessionV Where SV_CustNum = ''' + _SessionID + ''' And SV_Key In (' + RTrim(_VarNames) + ');';

Execute Immediate RTrim(@Sql);

Select * From #tempSVGM;

END;

Any insight into this would be greatly appreciated.

Dave Anderson

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Dec 01, 2014 at 05:13 PM

    Hi Dave,

    That would be expected. The temp table will continue to be available until it is dropped or the connection is closed.

    This behavior lets a Stored Procedure create and populate a temp table that is then available to further query at a later time if needed (i.e. another sql statement or another stored procedure)

    There are two possibilities.

    A) Use a TRY/CATCH block to create the table / delete the contents

    TRY

    Create table #tempSVGM ....
    CATCH ALL

    delete from #tempSVGM...
    END TRY

    B) Use an if exists with sp_GetTables
    if exists execute procedure sp_GetTables(null, null, 'tempSVGM', 'LOCAL TEMPORARY').....

    (I've not had a chance to try B, but I'm confident it would work).

    Edgar

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      We are using ADS 11.10.0.22 as well. The base table, however, is a Foxpro table. We suspect that there is an issue in the translator for this value. We should use the work around for now, unless you have other ideas or we are not importing or querying the Foxpro tables properly.

      Thank you again for your help in narrowing down what the issues were,

      Dave

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.