title: There is already an object named '#dirs' in the database error message even though I'm checking and dropping the temp table
body: <p>I'm not sure why I keep getting the object already exists message if I'm doing the check whether the object exists and I'm dropping it if it does. Anyone have any clues?</p>
  
  <p>Error message:</p>
  
  <p>Msg 2714, Level 16, State 1, Procedure spCreateDirectoryStructure, Line 54
  There is already an object named '#dirs' in the database.
  Msg 2714, Level 16, State 1, Procedure spCreateDirectoryStructure, Line 74
  There is already an object named '#dirs' in the database.</p>
  
  <p>Stored procedure:  </p>
  
  <pre><code>CREATE PROCEDURE spCreateDirectoryStructure
  AS 
         BEGIN
               SET NOCOUNT ON;
  
               DECLARE @year CHAR(4)
               DECLARE @month VARCHAR(2)
               DECLARE @day VARCHAR(2)
               DECLARE @root VARCHAR(200)
               DECLARE @dir VARCHAR(200)
               DECLARE @yearDir VARCHAR(200)
               DECLARE @monthDir VARCHAR(200)
               DECLARE @dayDir VARCHAR(200)
  
               SET @root = 'C:\Test\'
               SET @year = DATEPART(YEAR, GETDATE())
               SET @month = DATEPART(MONTH, GETDATE())
               SET @day = DATEPART(DAY, GETDATE())
               SET @yearDir = @root + @year + '\'
               SET @monthDir = @root + @year + '\' + @month + '\'
               SET @dayDir = @root + @year + '\' + @month + '\' + @day + '\'
  
              -- check root folder for year folder
               IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL 
                  BEGIN 
                        DROP TABLE #dirs
                  END
  
               CREATE TABLE #dirs (Directory VARCHAR(200))
  
               INSERT INTO #dirs
                      EXEC master.dbo.xp_subdirs 
                          @root
  
  
               IF NOT EXISTS ( SELECT Directory
                               FROM   #dirs
                               WHERE  Directory = @year ) 
                  EXEC master.sys.xp_create_subdir 
                      @yearDir
  
              -- **********************************************
              -- check year folder for month folder
  
               IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL 
                  BEGIN
                        DROP TABLE #dirs
                  END
               CREATE TABLE #dirs (Directory VARCHAR(200))
  
               INSERT INTO #dirs
                      EXEC master.dbo.xp_subdirs 
                          @yearDir
  
  
               IF NOT EXISTS ( SELECT Directory
                               FROM   #dirs
                               WHERE  Directory = @month ) 
                  EXEC master.sys.xp_create_subdir 
                      @monthDir
  
              -- **********************************************
              -- check month folder for day folder
  
               IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL 
                  BEGIN
                        DROP TABLE #dirs
                  END
               CREATE TABLE #dirs (Directory VARCHAR(200))
  
               INSERT INTO #dirs
                      EXEC master.dbo.xp_subdirs 
                          @monthDir
  
  
               IF NOT EXISTS ( SELECT Directory
                               FROM   #dirs
                               WHERE  Directory = @day ) 
                  EXEC master.sys.xp_create_subdir 
                      @dayDir
         END
  GO
  </code></pre>