Make directory in Using SQL xp_create_subdir

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

Below store procedure will create directory and sub directory also. Hope will it works for you.

CREATE PROCEDURE sp_CreateBackupPath 
(
     @directory_path as nvarchar(4000) ='F:\BACKUP\YEAR\MONTH\DAY' --For Local Computer directory
     --@directory_path varchar(256)='\\BACKUP\DBNAME\YEAR\MONTH\DAY'  --For Network Sharing directory
  )
AS

SET NOCOUNT ON
DECLARE @tmp_path varchar(256)
DECLARE @dir_name varchar(256)
DECLARE @idx int 
DECLARE @delimiter varchar(1) ='\'
DECLARE @folder_exists as int
DECLARE @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)


IF CHARINDEX(@delimiter,@directory_path) = 3
BEGIN
    SET @dir_name = LEFT(@directory_path, CHARINDEX(@delimiter,@directory_path))
    SET @tmp_path = REPLACE(@directory_path, @dir_name,'')
    
    SET @dir_name =REPLACE(@dir_name, @Delimiter, '')
END
ELSE IF CHARINDEX(@delimiter,@directory_path) = 1 and CHARINDEX(@delimiter,@directory_path,2) = 2
BEGIN
    SET @dir_name = LEFT(@directory_path, CHARINDEX(@delimiter,@directory_path,3))
    SET @tmp_path = REPLACE(@directory_path, @dir_name,'')
    
    SET @dir_name ='\\'+REPLACE(@dir_name, @Delimiter, '')

END
ELSE
BEGIN
    Print 'Path not valid.'
    RETURN
END

SET @idx = 1  
WHILE @idx!= 0       
BEGIN
    SET @idx = CHARINDEX(@Delimiter, @tmp_path)       

    IF @idx!=0       
    SET @dir_name = @dir_name + @Delimiter + LEFT(@tmp_path,@idx - 1)       
    ELSE       
    SET @dir_name = @dir_name + @Delimiter + @tmp_path       
            
    IF LEN(@dir_name) > 0
    BEGIN
        DELETE FROM @file_results
        INSERT INTO @file_results
        (file_exists, file_is_a_directory, parent_directory_exists)
        EXEC MASTER.dbo.xp_fileexist @dir_name

        SELECT @folder_exists = file_is_a_directory
        FROM @file_results

        IF @folder_exists = 0
        EXECUTE master.dbo.xp_create_subdir @dir_name       
    END
   
    SET @tmp_path = RIGHT(@tmp_path, LEN(@tmp_path) - @idx)       
    IF LEN(@tmp_path) = 0 BREAK   
End
PRINT @directory_path +  ' created on  ' + @@servername
SET NOCOUNT OFF

By using above stored procedure ,

EXEC sp_CreateBackupPath 'F:\BACKUP\YEAR\MONTH\DAY'

or

EXEC sp_CreateBackupPath '\\BACKUP\DBNAME\YEAR\MONTH\DAY'

Hope it will works for you. Thanks you.!

Dynamic script using your logic.

CREATE PROCEDURE SPCheckDirectoryExists
(
    @chkdirectory as nvarchar(4000)
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @pos INT,@NewPath AS NVARCHAR(256) 
    WHILE(charindex('\',@chkdirectory) > 0)
    begin
        select  @pos  = CHARINDEX('\',@chkdirectory,0)  
        IF (@NewPath <> '')
            SET @NewPath = @NewPath + '\'
        SET @NewPath =ISNULL(@NewPath,'') + SUBSTRING(@chkdirectory,0,CHARINDEX('\',@chkdirectory,@pos-1)) 

        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 @NewPath

        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 @NewPath
            PRINT @NewPath +  ' created on  ' + @@servername
            END       
        ELSE
            PRINT 'Directory already exists'

        SET @chkdirectory = SUBSTRING(@chkdirectory,@pos+1,len(@chkdirectory))
    END
END