Create A Folder With T-SQL - SQLServerCentral By Howard Dunn, 2012/10/16

Create A Folder With T-SQL - SQLServerCentral


/***************************************************************************
***                                                                      ***
*** Title : usp__CreateDirectory                                                   ***
*** Description : Pass a path to the SP and it will create the               ***
*** the relevant folder for you.                                                   ***
***                                                                                        ***
***************************************************************************/

CREATE PROCEDURE usp__CreateDirectory
 @NewFolder    varchar(500)
AS

SET NOCOUNT ON
-- Create a table variable to hold the results of xp_fileexist --
DECLARE @DoesFolderExist
TABLE
(
 FileExists int NOT NULL,
 FileIsDirectory int NOT NULL,
 ParentDirExists int NOT NULL
)
-- Create a table variable to hold the results of xp_fixeddrives --
DECLARE @Drives
TABLE
(
 Drive char(1) NOT NULL,
 FreeSpace int    NOT NULL
)
-- Grab a list of available drives and inse them into the @Drives table vairable --
INSERT INTO @Drives
EXEC master.dbo.xp_fixeddrives
-- Does the drive exist, if not - stop here --
IF LEFT(@NewFolder, 1) NOT IN (SELECT Drive FROM @Drives)
BEGIN
 PRINT 'That drive does not exist'
 RETURN
END
-- Check to see if the folder already exists, if not, create it --
INSERT INTO @DoesFolderExist
EXEC master.dbo.xp_fileexist @NewFolder
IF (SELECT FileIsDirectory FROM @DoesFolderExist) = 0
BEGIN
 EXECUTE master.dbo.xp_create_subdir @NewFolder
 PRINT 'Directory Created'
END
ELSE
 PRINT 'Directory Already Exists'
SET NOCOUNT OFF