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.
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; go create proc #sp_today as select getdate() as dt;
You should search for it in
tempdb, not in current db