T-SQL Script to Check/Create directory

Recently I came across a situation where I need to check whether the directory is exists or not, in case if the directory does not exist, I have to create new one.

As a solution, I have created below script to fix the issue.

[sourcecode language="sql"]
declare @chkdirectory as nvarchar(4000)
declare @folder_exists as int

set @chkdirectory = 'C:\SQLDBPool\SQL\Articles'

declare @file_results table
(file_exists int,
file_is_a_directory int,
parent_directory_exists int
)

insert into @file_results
(file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @chkdirectory

select @folder_exists = file_is_a_directory
from @file_results

--script to create directory
if @folder_exists = 0
begin
print 'Directory is not exists, creating new one'
EXECUTE master.dbo.xp_create_subdir @chkdirectory
print @chkdirectory + 'created on' + @@servername
end
else
print 'Directory already exists'
GO [/sourcecode]