How to create a subfolder in a SQL Server FileTable

SQL Server 2012

The scope of this post is that to explain how to create a subfolder in a SQL Server FileTable (Documents). I suppose that the FileTable already exists.

I want to create a subfolder like this:

{root}\{folder1}\{folder2}\{filename}

and to get the new hierarchyID for the path, I use this formula:

parentpath.ToString()

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  1, 6))) + '.'

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  7, 6))) + '.'

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  13, 4))) + '/'

This is the T-SQL code:

CREATE PROCEDURE [dbo].[SetDocumentContent]

                @IdFileData bigint,

                @file_stream image

AS

BEGIN

                DECLARE @FileName varchar(255)

                DECLARE @DocumentClassification varchar(50)

                DECLARE @ETSIdImpianto int

                DECLARE @DocumentClassification_path_locator hierarchyid

                DECLARE @ETSIdImpianto_path_locator hierarchyid

                DECLARE @FileName_path_locator hierarchyid

                -- Metadata to identify the correct document. The FileData is my internal table.            

SELECT @FileName = Name, @DocumentClassification = DCT.Description, @ETSIdImpianto=ETSIdImpianto

                               FROM FileData FD INNER JOIN DocumentClassificationType DCT

                               ON FD.IdDocumentClassificationType = DCT.Id  WHERE FD.Id = @IdFileData

                -- 1. If folder1 exists      

                SELECT @DocumentClassification_path_locator = path_locator FROM Documents

                WHERE name = @DocumentClassification AND is_directory = 1

                IF (@DocumentClassification_path_locator IS NULL)

                BEGIN

                               INSERT INTO Documents

        ( name

        , is_directory)

    VALUES

        ( @DocumentClassification

        , 1)

                               -- Get the folder1

                               SELECT @DocumentClassification_path_locator = path_locator FROM Documents

                               WHERE name = @DocumentClassification AND is_directory = 1

                END

                -- 2. If folder2 exists      

                SELECT @ETSIdImpianto_path_locator = path_locator FROM Documents

                WHERE name = CAST(@ETSIdImpianto as varchar) AND is_directory = 1

                IF (@ETSIdImpianto_path_locator IS NULL)

                BEGIN

                               INSERT INTO Documents

        ( name                                                       

        , is_directory

                                                               , path_locator)

    VALUES

        ( CAST(@ETSIdImpianto as varchar)

        , 1

                                                               , @DocumentClassification_path_locator.ToString()

        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                         1, 6))) + '.'

        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                         7, 6))) + '.'

        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                         13, 4))) + '/')

                               -- Get the folder2

                               SELECT @ETSIdImpianto_path_locator = path_locator FROM Documents

                               WHERE name = CAST(@ETSIdImpianto as varchar) AND is_directory = 1

                END

                -- 3. The FileName

                SELECT @FileName_path_locator = path_locator FROM Documents

                WHERE name = @FileName AND is_directory = 0 AND parent_path_locator = @ETSIdImpianto_path_locator

                IF (@FileName_path_locator IS NULL)

                BEGIN

                               INSERT INTO Documents

        ( name                                                       

                                                               , file_stream       

                                                               , path_locator)

    VALUES

        ( @FileName

                                                               , @file_stream

                                                               , @ETSIdImpianto_path_locator.ToString()

        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                         1, 6))) + '.'

        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                         7, 6))) + '.'

        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                         13, 4))) + '/')                           

                               SELECT @FileName_path_locator = path_locator FROM Documents

                               WHERE name = @FileName AND is_directory = 0 AND parent_path_locator = @ETSIdImpianto_path_locator

                END

Bye!