cancel
Showing results for 
Search instead for 
Did you mean: 

Store Procedures do not delete temp tables created in that procedure

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

Edgar,

We tried your first example to do a create of the temporary table but the following Execute Immediate throws an error when it is changed to an Insert Into statement.  The records still seem to get populated, at least when tried in the sql query window (not tried in the sp yet).  I am not sure why an error is thrown and it a non-descript ISAM error.  We also tried to drop the table in the try catch instead of creating the table and leaving the select into statement as is, but it says that it is locked and does not drop the table, so this does not work either.  Are we going to have to trap the execute immediate error as well and just ignore?

Thank you,

Dave Anderson

Former Member
0 Kudos

Not sure.  A quick test didn't reproduce the issue.  Can you tweak the following to show me the error?

try

create table #temp (id integer);

catch all

delete from #temp;

end try;

execute immediate 'insert into #temp (id) values (1)';

select * from #temp;

Former Member
0 Kudos

Below is the stored procedure we are trying to create.  Below that is the ISAM error we get when doing the insert into the temporary table.  It is very similar to your example but the select is coming out of a temporary table.  The parameters used were '0000321234695246' and '''Cust_Delivery_Info'',''Pickup_Delivery'''.  I have tried removing the "And SV_Key In(..." statement but I get the same error either way.  Any insights would be helpful and appreciated.

Create PROCEDURE SessionVariableGetMany

   (

      SessionId CHAR ( 16 ),

      VarNames CHAR ( 2000 ),

CURSOR VARYING OUTPUT

   )

BEGIN

  Declare @Sql char(2000);

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

  Try

    Create Table #tempSVGM (SV_Key Char( 50 ), SV_Value Char( 254 ));

  Catch All

    Delete From #tempSVGM;

  End Try;

  Execute Immediate RTrim(@Sql);

  Select * From #tempSVGM;

END;

poQuery: Error 7200:  AQE Error:  State = HY000;   NativeError = 5154;  [iAnywhere Solutions][Advantage SQL][ASA] Error 5154: 

Execution of the stored procedure failed.   Procedure Name: SessionVariableGetMany. Error 7200:  AQE Error:  State = S0000;  

NativeError = 2004;  [iAnywhere Solutions][Advantage SQL Engine][ISAM]ISAM error <identifier> - EXECUTE IMMEDIATE  --

Location of error in the SQL statement is: 310 (line: 10 column: 21)

Former Member
0 Kudos

Hmm...

This works for me.  Just changed the base table in the select statement to one I had already.

I was running this on 11.10.0.20 (and upgraded to 11.10.0.22 just in case it introduced something)

Some things that come to mind. 

A) Is there anything logged into the ads_err.adt / ads_err.dbf at the same time?

B) Is it possible you already have another temp table created with a bit different structure?  - If you are using ARC, maybe close the ARC SQL window and run it again?

Are you running this in a different client?  Shouldn't matter, but just checking.

Former Member
0 Kudos

We found the issue.  It has to do with null values in the fields of the table being selected from that are to be inserted into the temporary table.  Even though the temporary table is created to accept nulls when the insert is attempted it fails with the ISAM error.  If the select is changed to do an IsNull(<fieldName>. '') the process completes without failure.  There seems to be an issue with inserting nulls into temporary tables.

Former Member
0 Kudos

I'm glad you were able to work it out, but that again is odd.

What version of ADS?  Also, what table type is the base table?  Just tested with 11.10.0.22 and ADT base table and it works ok.   Maybe some other combination is needed.

Edgar

Former Member
0 Kudos

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