Verify Directory Exists and Create if Necessary

--Create By:Doug Lineberry
--Create On:2005-03-07
--Purpose:This script will check to see if a directory exists and if it does not then it will create the directory.

declare @year Char(4)
declare @cmd sysname
declare @dir varchar(200)
declare @direxists int
select @year = DATEPART(YY, GETDATE())
set @dir = 'c:\Temp\'

create table GetDirDetails_temp
(subdirectory varchar(100),depth int)

insert into GetDirDetails_temp
(subdirectory, depth)
exec master..xp_dirtree @dir

set @direxists = (select count(*) from GetDirDetails_temp where subdirectory = @year and depth = 1)

begin
-- * Check if direxists val = 1
if @direxists = 0
begin
Raiserror('Directory of Current Year Does Not Exist',16,1) with seterror
set @cmd = 'mkdir ' + @dir + @year + '\'
exec master..xp_cmdshell @cmd, no_output
end
else
print 'Directory of Current Year Does Exist'
end
drop table GetDirDetails_temp