-----code:start-----
/***************************************************************************
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 variable --
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
-----code:end-----
2. Run the stored procedure
-----code:start--------------------------
exec usp_CreateDirectory 'D:\Test\Test1'
-----code:end--------------------------
Results Messages:
Directory Created
3. Try to create folder with same name again
-----code:start-------------------------------
exec usp_CreateDirectory 'D:\Test\Test1'
-----code:end---------------------------------
Results Messages:
Directory Already Exists
4. Try to create a folder in does not exist drive
-----code:start--------------------------
exec usp_CreateDirectory 'G:\Test\Test1'
-----code:end--------------------------
Results Messages:
That drive does not exist
Source:
http://www.sqlservercentral.com/scripts/Create+a+folder/93674/
Article: Create A Folder With T-SQL
By Howard Dunn