Table Exists, But Can't Drop It

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 2000 Forums

SQL Server Development (2000)

Table Exists, But Can't Drop It

Author

Topic

BarryC
Starting Member

6 Posts

Posted - 2007-12-26 : 10:33:28

I have the following code creating a temp table. This code produces errors which I don't know how to correct.

**************************************************
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_Radiant_NotReady'
AND type = 'P')
DROP PROCEDURE sp_Radiant_NotReady
GO

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = '#OPStatuses')
DROP TABLE #OPStatuses
GO

CREATE PROCEDURE sp_Radiant_NotReady
@BeginDate Datetime = '12/19/2007 00:00:00',
@EndDate Datetime = '12/20/2007 23:59:59'
-- If you don't want to include the BeginDate
-- you must have BeginDate like '12/19/2007 23:59:59'
AS
SELECT @p1, @p2

Create table #OPStatuses
(
[agentID] [int],
[eventDateTime] [datetime],
[gmtOffset] [smallint],
[eventType] [tinyint],
[reasonCode] [smallint]
)

SELECT asd.[agentID], asd.[eventDateTime], asd.[eventType], asd.[reasonCode]
INTO #OPStatuses
FROM [db_cra].[dbo].[AgentStateDetail] asd
WHERE asd.eventDateTime > @BeginDate -- For dates, This is like >= in Oracle
AND asd.eventDateTime < @EndDate
AND (asd.[eventType] = 1
OR asd.[eventType] = 7)

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE sp_Radiant_NotReady

GO
DROP TABLE #OPStatuses
GO
********************************************************

Produces these Errors:

************************************************************
Server: Msg 2714, Level 16, State 1, Procedure sp_Radiant_NotReady, Line 22
There is already an object named '#OPStatuses' in the database.
Server: Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'sp_Radiant_NotReady'.
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#OPStatuses', because it does not exist in the system catalog.
**************************************************************

Any help is appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-26 : 10:46:23

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_Radiant_NotReady'
AND type = 'P')
DROP PROCEDURE dbo.sp_Radiant_NotReady
GO

CREATE PROCEDURE dbo.sp_Radiant_NotReady
@BeginDate Datetime = '12/19/2007 00:00:00',
@EndDate Datetime = '12/20/2007 23:59:59'
-- If you don't want to include the BeginDate
-- you must have BeginDate like '12/19/2007 23:59:59'
AS
SELECT @p1, @p2

Create table #OPStatuses
(
[agentID] [int],
[eventDateTime] [datetime],
[gmtOffset] [smallint],
[eventType] [tinyint],
[reasonCode] [smallint]
)

Insert into #OPStatuses
SELECT asd.[agentID], asd.[eventDateTime], asd.[eventType], asd.[reasonCode]
FROM [db_cra].[dbo].[AgentStateDetail] asd
WHERE asd.eventDateTime > @BeginDate -- For dates, This is like >= in Oracle
AND asd.eventDateTime < @EndDate
AND (asd.[eventType] = 1
OR asd.[eventType] = 7)

DROP TABLE #OPStatuses
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE sp_Radiant_NotReady
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-26 : 21:47:13

And the following code is wrong... you cannot check local SysObects for the existance of Temp Tables this way...

IF EXISTS (SELECT name 
FROM sysobjects 
WHERE name = '#OPStatuses') 
DROP TABLE #OPStatuses
GO

If you must check for the existance of temp tables, one of the proper ways to do it is...

     IF OBJECT_ID('TempDB..#temptablename','U') IS NOT NULL
   DROP TABLE #temptablename

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources