if exists (select 'SP exists' from sysobjects where name = 'usp_create_folder' and xtype = 'p')
	drop proc usp_create_folder
go
set nocount on
go
create procedure usp_create_folder @foldnm varchar(250)
as
BEGIN
--	Create By:	Regan Galbraith
--	Create On:	2004-07-16
--	Purpose:	
--		This stored procedure was written to create a folder.
--		It determines whether the fodler exists, and reports errors if it does
--		
--
--	Example:
--		exec usp_create_folder 'd:\backups\backup20040716'
--
--	Possible future additions:
--
--	Change Control:	version 1 - creation and adding of comment
--
declare @SQL		varchar(500)
declare @ErrorType	varchar(25)

create table #direxists
(FileExists int,
FileIsDir int,
ParentDirExists int)

insert into #direxists exec master..xp_fileexist @foldnm
if exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
	begin
	select @ErrorType 'Folder exists'
	goto DirError
end
else
	begin
	select @sql = 'master..xp_cmdshell ''md '+ @foldnm +''''
	exec (@sql)
	insert into #direxists exec master..xp_fileexist @foldnm
	if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
		begin
		select @ErrorType 'Folder Not Created'
		goto DirError
	end
	print 'New Backup Directory '+@foldnm+' has been created'
end
goto finish	

DirError:
Print '*** ERROR '+@ErrorType+' ***'
drop table #direxists
RETURN -1

Finish:
drop table #direxists
RETURN 0

END

set nocount on

declare @new_backup_folder varchar(250)
declare @dateformat	char(8)
declare @rc int

select @dateformat = convert(char(2),(datepart(day,getdate()) ))
	+substring(convert(char(3),(datepart(month,getdate()) +100) ),2,2)
	+convert(char(4),datepart(year,getdate()))
--debug display
--select @dateformat
--base folder
select @new_backup_folder = 'e:\Scrubbed_Backups\'+@dateformat
--debug display
--select @new_backup_folder
exec @rc=usp_create_folder @new_backup_folder
select @rc