Board index » delphi » problem Using SQLServer temporary tables (#localtemp not the ##global type)

problem Using SQLServer temporary tables (#localtemp not the ##global type)

I have used local temp tables in the past in stored procedures.
Hoever, I never used them in a stored procedure after I created them in a
prior stored procedure.
I have 2 Stored Procedures
1)CreateTempTable
which has the text
 CREATE TABLE #tmpTbl1
( ID int
)

2)AddTempTableValues which has the text
INSERT INTO  #tmpTbl1  VALUES(@ID)
    where @ID is the parameter

Problem is when I execute the AddTempTableValues stored procedure after I
execute the CreateTempTable stored proc, I get an Object #tmpTbl1 not found
error.Is the scope of the object limited to the first SP? Does the local
connection as defined by SQL Server terminate after the first SP and a new
one created for the second one? I use the same ADOConnection object. Also it
doesnt work when I try this in SQLQueryAnalyzer .  THis only works if I use
##tmptbl1 as the name, but I dont want to use it since another user will be
able to access the table.

The idea os using temp tables is so I can fill the temptable with a list of
IDs by executing the AddTemptblValues stored proc eaccth time for every ID
in a stringlist of IDs. I can then test set membership from this list .
Example: I can use the temptable which stores a list of customer IDs in a
third stored proc
SELECT * FROM CUSTOMERTABLE WHERE CUSTOMERID IN (SELECT ID AS CUSTOMERID
FROM #tmpTable1)

I would like to avoid the workaround which would be to send a long string of
IDs delimited by commas as a varchar(8000) parameter and then testing
whether an ID falls in the list of IDs in the stringlist.

 

Re:problem Using SQLServer temporary tables (#localtemp not the ##global type)


Solved my problem. It turns out that I can't create a local temptable in one
stored procedure and then use the very same table in another SP. It gets
destroyd after the SP where it gets created has finished executing.

So we need to create the local #temptable in a regular SQL statement (don't
use EXEC, just run the CREATE statement) .
After that we are free to use that local #temptable as many times as we
choose through Stored Procs as long as we don't disconnect in between.

Re:problem Using SQLServer temporary tables (#localtemp not the ##global type)


Hi Pravin!

On Wed, 1 Dec 1999 16:47:11 -0500, "Pravin"

Quote
<pravinrat...@infocure.com> wrote:
>Solved my problem. It turns out that I can't create a local temptable in one
>stored procedure and then use the very same table in another SP. It gets
>destroyd after the SP where it gets created has finished executing.

>So we need to create the local #temptable in a regular SQL statement (don't
>use EXEC, just run the CREATE statement) .
>After that we are free to use that local #temptable as many times as we
>choose through Stored Procs as long as we don't disconnect in between.

Thank you for posting that note.

tomi.

Other Threads