Make directory in Using SQL xp_create_subdir

Question

CREATE PROCEDURE SPCheckDirectoryExists
  (
     @chkdirectory as nvarchar(4000)
  )
  AS
  SET NOCOUNT ON
  BEGIN
     DECLARE @folder_exists as int
     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
        EXECUTE master.dbo.xp_create_subdir @chkdirectory
        PRINT @chkdirectory +  ' created on  ' + @@servername
     END       
    ELSE
    PRINT 'Directory already exists'
END

By using above stored procedure ,

EXEC SPCheckDirectoryExists '\\SampleNetworkpath\Test\Test1'

It will check and create Test1 folder in Test Folder ,

If Test Folder is not present in \\SampleNetworkpath\Path Its throwing Error ,

How to Fix this issue , Thanks in Advance,

Answer 1

You need to check that directory as well. Something like this, using your same logic...

CREATE PROCEDURE SPCheckDirectoryExists
  (
     @chkdirectory as nvarchar(4000)
  )
  AS
  SET NOCOUNT ON
  BEGIN
     DECLARE @folder_exists as int
     DECLARE @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)

     DECLARE @folder_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)
     DECLARE @chkdirectory2 nvarchar(4000) = reverse(right(reverse(@chkdirectory),len(@chkdirectory) - charindex('\',reverse(@chkdirectory))))
     DECLARE @folder_exists2 int

    INSERT INTO @file_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC MASTER.dbo.xp_fileexist @chkdirectory

    INSERT INTO @folder_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC MASTER.dbo.xp_fileexist @chkdirectory2

    SELECT @folder_exists = file_is_a_directory
    FROM @file_results

    SELECT @folder_exists2 = file_is_a_directory
    FROM @folder_results

    --script to create directory        
    IF @folder_exists = 0 and @folder_exists2 = 1
     BEGIN
        EXECUTE master.dbo.xp_create_subdir @chkdirectory
        PRINT @chkdirectory +  ' created on  ' + @@servername
     END       
    ELSE
    PRINT 'Directory already exists or parent directory was invalid'
END