removing a temp table in SQL server?

Question

I am having problems removing a temp table in SQL server.

I have a stored procedure but when I run in via my application it says:

"There is already an object named '#WeekList' in the database"

when i try to drop the table i get the following message:

Cannot drop the table '#WeekList', because it does not exist or you do not have permission.

My SP is as follows:

USE [test_staff]
GO
/****** Object:  StoredProcedure [dbo].[sp_create_week_list]    Script Date: 03/20/2012 09:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sp_create_week_list]

AS

CREATE TABLE #WeekList
    (
    month_date date
    )


DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2011-01-19T00:00:00'
--SELECT @REPORT_DATE = GETDATE() -- should grab the date now.
SELECT @WEEK_BEGINING = 'MONDAY'
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))
PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)

DECLARE @Interval int = datediff(WEEK,getdate(),@WEEK_START_DATE)+1

SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList

WHILE @Interval <= 0
    BEGIN
    set @WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE)
    set @WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE)
    INSERT INTO #WeekList values (@WEEK_START_DATE,@WEEK_END_DATE)
    SET @Interval += 1;
    END

SELECT 
CONVERT(VARCHAR(11), Start_Week, 106) AS 'Start',
CONVERT(VARCHAR(11), End_Week, 106) AS 'End',
DATEDIFF(DAY, 0, Start_Week) / 7 AS week_ref   -- create the unique week reference number

FROM #WeekList
ORDER BY Start_Week DESC

DROP TABLE #WeekList

Solution 1

In the SP there is a problem in following statement.

SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList

Instead of that try this one, it may work.

INSERT INTO #WeekList
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE

Solution 2

You can drop the #YourTable temporary table using this code:

if exists (select * from tempdb.sys.tables where name like '#YourTable%')
    drop table #YourTable

Best place to do this is right before you run insert into to create the table.

Solution 3

--Check if it exists

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
BEGIN
drop table #temptable
END

and while using temporary table insert, specify column names externally like

insert into #temptable(col1,col2..) values(1,2,..)

Solution 4

In the middle of the code you have:

select ...  
into #WeekList

This will try to create a new temporary table called #WeekList which you'd already created using the CREATE TABLE statement at the top of the proc

If you change the insert to the style

insert into #WeekList (columns)  
select ...

Then you will get rid of the error

Solution 5

The only thing I can see that could hint at what's wrong there is the lack of BEGIN and END? I use this all the time, but I always put BEGIN and END around the sprocs code...

View more solutions

Comments

[Tim Schmelter]

"it's more better option" Would you care to explain?

[SOLDIER-OF-FORTUNE]

I have also created a physical table in the DB. [dbo].[WeekList] and still get the same problem. Only difference is i can drop the table.

[KuldipMCA]

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable), Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data

[SOLDIER-OF-FORTUNE]

Kuldip can you provide us with an example?

[KuldipMCA]

@sp-1986 i don't see any other problem in your code if drop will give you error then check in tempdb that #temp table exist or not then you can run your drop command.

[KuldipMCA]

for help - databases.aspfaq.com/database/…

[SOLDIER-OF-FORTUNE]

Sorry didnt quite get that? did you mean: SELECT Start_Week=@WEEK_START_DATE , End_Week=@WEEK_END_DATE INTO #WeekList

[SOLDIER-OF-FORTUNE]

Column name or number of supplied values does not match table definition. Any ideas?

[kaj]

Yes. That statement will try to create a new table called #WeekList. Also note your original #WeekList table definition has a single column whereas you're using it subsequently expecting two columns

[Brijesh Patel]

there is problem in declare table statement. you need to add two column as define below. it will work fine. CREATE TABLE #WeekList ( Start_Week date, End_Week date )

[Andomar]

+1 Explanation of "the problem": select ... into tries to create a table, but there is already a create table at the top of the stored procedure.

[Ashish Gupta]

@SOLDIER-OF-FORTUNE - Corrected the script. This should work now.

[Ive]

Pleas dont use this exists (select * from tempdb.sys.tables where name like '#YourTable%') use insteed if (OBJECT_ID('tempdb.sys.#YourTable') IS NOT NULL)