title: TSQL Temporary Procedure: There is already an object named
body: <p>A similar question has been answered, but only for temporary tables.</p>
  
  <p>I want to use a temporary stored procedure, as per</p>
  
  <pre><code>-- test if folder exists, else create it
  create procedure #mycheckcreatefolder
  (
  @rootfolder NVARCHAR(MAX),
  @subfolder NVARCHAR (MAX)
  )
  as
  BEGIN
        declare @full_path as NVARCHAR(MAX)
        declare @folder_exists as int
        declare @file_results table
        (file_exists int, file_is_a_directory int, parent_directory_exists int)
        select @full_path = @rootfolder + @subfolder
  
        insert into @file_results
        (file_exists, file_is_a_directory, parent_directory_exists)
        exec master.dbo.xp_fileexist @full_path
  
        select @folder_exists = file_is_a_directory
        from @file_results
  
        --script to create directory        
        if @folder_exists = 0
         begin
            print 'Directory does not exist, creating new one'
            EXECUTE master.dbo.xp_create_subdir @full_path
            print @full_path +  'created on' + @@servername
         end       
        else
          print 'Directory already exists'
  END;
  GO
  
  -- use the stored procedure
  </code></pre>
  
  <p>but when I execute the query repeatedly I get</p>
  
  <pre><code>Msg 2714, Level 16, State 3, Procedure #mycheckcreatefolder, Line 30
  There is already an object named '#mycheckcreatefolder' in the database.
  </code></pre>
  
  <p>Line 30 is the "print 'Directory already exists'".</p>
  
  <p>I have removed all code below that, the error still pops up.</p>
  
  <p>Also adding</p>
  
  <pre><code>drop procedure #mycheckcreatefolder
  </code></pre>
  
  <p>at the end does not help, as does a conditional drop in the beginning, since then I get the error that the "create procedure" needs to be the first instruction.</p>
  
  <p>What is wrong?</p>