SP won't drop temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP won't drop temp table

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-11-29 : 09:06:16

If I execute the script below in the Query Analyser it works fine. If I put it into a stored proc and execute the storec proc in the QA it fails.

IF (EXISTS (SELECT name, type
FROM tempdb..sysobjects
WHERE (name like '#r%')))
DROP TABLE #r
CREATE TABLE #r (FirmID int)

Error:
Server: Msg 3701, Level 11, State 5, Procedure proc_ChristmasList, Line 44
Cannot drop the table '#r', because it does not exist in the system catalog.

SELECT name, type
FROM tempdb..sysobjects
WHERE (name like '#r%')

results in:
#r____________ ... (lots more here) ..._____________000000008858

which tells me it is there.

What else should I do?

Declan

-dw

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-29 : 09:12:25

[code]IF EXISTS
(SELECT name, type
FROM tempdb..sysobjects
WHERE name like '#r%')
begin
DROP TABLE #r
end
CREATE TABLE #r (FirmID int)

[/code]

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-29 : 09:14:11

[code]If Object_ID('tempdb..#r') is not null
Drop Table #r
GO

Create Table #r
(
...
)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-29 : 09:17:02

Normally you dont need to drop temp tables
They are destroyed automatically when SP exits

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-29 : 09:44:01

You can only drop temp tables that you created.

To check if a temp table exists and drop it if it does (SQL 2005 only):

if object_id('tempdb..#r','U') is not null drop table #r

CODO ERGO SUM

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-11-29 : 10:26:55

quote:
Originally posted by evilDBA

Normally you dont need to drop temp tables
They are destroyed automatically when SP exits

I initially developed this SP in Query Analyser. I now is about 160 lines.

When using QA I had to drop the temporary tables with script as shown above in order to run the script again.

Copying the script from QA to a stored proc gave me the problem.

As you say, evilDBA, the temp tables are dropped at end of SP so all I had to do was remove the script checking for and dropping the temporary tables and it works just fine.

Thanks,
Declan

-dw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources