TSQL Temporary Procedure: There is already an object named

Question

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)
)
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

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?

Answer 1

if object_id('tempdb..#sp_today') is not null drop proc #sp_today;
go

create proc #sp_today as
   select getdate() as dt;

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