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!
