TSQL Temporary Procedure: There is already an object named

A similar question has been answered, but only for temporary tables.

I want to use a temporary stored procedure, as per

-- test if folder exists, else create it
create procedure #mycheckcreatefolder
@rootfolder NVARCHAR(MAX),
@subfolder NVARCHAR (MAX)
      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
          print 'Directory does not exist, creating new one'
          EXECUTE master.dbo.xp_create_subdir @full_path
          print @full_path +  'created on' + @@servername
        print 'Directory already exists'

-- use the stored procedure

but when I execute the query repeatedly I get

Msg 2714, Level 16, State 3, Procedure #mycheckcreatefolder, Line 30
There is already an object named '#mycheckcreatefolder' in the database.

Line 30 is the "print 'Directory already exists'".

I have removed all code below that, the error still pops up.

Also adding

drop procedure #mycheckcreatefolder

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.

What is wrong?


  • if object_id('tempdb..#sp_today') is not null drop proc #sp_today;
    create proc #sp_today as
       select getdate() as dt;

    You should search for it in tempdb, not in current db