title: removing a temp table in SQL server?
body: <p>I am having problems removing a temp table in SQL server.</p>
  
  <p>I have a stored procedure but when I run in via my application it says: </p>
  
  <blockquote>
    <p>"There is already an object named '#WeekList' in the database"</p>
  </blockquote>
  
  <p>when i try to drop the table i get the following message:</p>
  
  <blockquote>
    <p>Cannot drop the table '#WeekList', because it does not exist or you do
    not have permission.</p>
  </blockquote>
  
  <p>My SP is as follows:</p>
  
  <pre><code>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 &lt;= 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
  </code></pre>