Temp Tables

Question

Post reply

Temp Tables

satrigney

SSC Eights!

Points: 926
More actions
August 6, 2001 at 9:46 am

#101006

I have a TSQL Problem. I can not seem to reuse a temp table name. We are trying the below code, and it will not run.
The error we are getting is:
Server: Msg 2714, Level 16, State 2, Line -1
There is already an object named '#tmpReportClient' in the database.
The code is:
DROP TABLE #tmpReportClient
DECLARE @iReportID AS Int
DECLARE @iParentClientID AS Int
SET @iReportID = 69
SET @iParentClientID = 7887
SELECT tblReportClient.iClientID INTO #tmpReportClient
FROM tblReportClient
INNER JOIN tblClient
ON
tblReportClient.iClientID = tblClient.iClientID
WHERE
tblReportClient.iReportID = @iReportID
IF @@ROWCOUNT = 0
BEGIN
DROP TABLE #tmpReportClient
SELECT DISTINCT iClientID INTO #tmpReportClient FROM tblClient WHERE iParentClientID = @iParentClientID
END
SELECT * FROM #tmpReportClient
------------------------------
Is the re-use of #tmpReportClient not allowed?

Viewing 6 posts - 1 through 5 (of 5 total)

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

GRN Hall of Fame Points: 3260 More actions · Answer 1

Try dropping the temp table right after the select rather than at the top

SELECT * FROM #tmpReportClient

DROP Table #tmpReportClient

satrigney SSC Eights! Points: 926 More actions · Answer 2

I still get the same error, that There is already an object named '#tmpReportClient' in the database.

GRN Hall of Fame Points: 3260 More actions · Answer 3

i think i see what the problem is ...

try declaring the table explicitly

CREATE TABLE #tmpReportClient

...

instead of SELECT INTO

better still you can put the whole code in a procedure and use table variables or temp tables..if you can ,stick to table variables ..(this is only available in SQL 2000)

sxnandwa Right there with Babe Points: 716 More actions · Answer 4

Which version of SQL Server are you using.If it is 6.5 then you cannot create and drop a temporary table in the same stored procedure and use it again.

May be it has changed in SQL 2000 but for 6.5 it is not possible to use the same temporary table name again in same stored procedure.Check on Temproary Tables in BOoks Online for details.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 5

As part of your cleanup , y ou should delete the temp table at the end of your stored proc. It may not seem logical but the results have already been passed to the caller and the temp table is no longer necessary. Unless you have other processing that relies on it, it would be good coding pratice to clean up and release any resource you create as part of a procedure.