Trying to insert stream_id from FileTable into SQL table that has a "stream

I'm trying to add stream_id from SQL Server FileTable into the stream_id column in category table using the second insert statement in the code below. I am following the code of Dan but after the stored procedure runs, stream_id column is still NULL. I do not know if the reason is that the value I get from GetPathLocator() function does not equal the path_locator column in the FileTable or there is something wrong with the stored procedure. Here is an image of the queried FileTable.

This is the stored procedure code:

ALTER PROCEDURE [dbo].[SpCategory_AddCategory] 
     @CategoryName nvarchar(100),
     @RelativeFilePath nvarchar(MAX),
     @Username nvarchar(100),
     @FileName nvarchar(1000),
     @Id int = 0 output

AS
BEGIN
    
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max),
     @attributes nvarchar(10),
     @tablename nvarchar(50),
     @filenameStr nvarchar(1000),
     @relativeFP nvarchar(MAX)
    SET @filenameStr = @FileName
    SET @relativeFP = @RelativeFilePath;
    SET @attributes = N' * '
    SET @tablename = N'FileData '
    SET @sql = N'SELECT '   CHAR(39)   @filenameStr   CHAR(39)   N', '   @attributes   
               N' FROM '   N'OPENROWSET(BULK '   CHAR(39)  @relativeFP   CHAR(39)   ', SINGLE_BLOB)'   N' AS '   @tablename
    
    INSERT INTO [dbo].[LibraryTable] ([name],[file_stream])
    EXEC(@sql)

    DECLARE @FileTableRoot varchar(1000);
    SELECT @FileTableRoot = FileTableRootPath('dbo.LibraryTable');  
    INSERT INTO dbo.[Category] (categoryName, stream_id, userId)
    VALUES(@CategoryName, (SELECT stream_id
                            FROM dbo.LibraryTable
                            WHERE path_locator = GetPathLocator(CONCAT(@FileTableRoot, @relativeFP))) , (SELECT id FROM dbo.[User] WHERE dbo.[User].username = @Username))
    
    Select @Id = SCOPE_IDENTITY();

END

This is the execution of the stored procedure:

USE [LibraryMS]
GO

DECLARE @return_value int,
        @Id int

EXEC    @return_value = [dbo].[SpCategory_AddCategory]
        @CategoryName = N'Encyclopedia',
        @RelativeFilePath = N'C:\Users\mahmo\Desktop\NTNU_Backup\Code_Exercises\c_sharp_exercises\PROJECT_library_management_system\Images\Images\Encyclopedias.jpg',
        @Username = N'mamo_00',
        @FileName = N'Encyclopedias.jpg',
        @Id = @Id OUTPUT

SELECT  @Id as N'@Id'

SELECT  'Return Value' = @return_value

GO

CodePudding user response：

I managed to fix my problem while i was in the process of dismantling my stored procedure to see exactly why it did not work. What i did was creating two additional stored procedures, one to handle loading files into the FileTable and one to insert the data into the Category table. I did this to see if any of those part where fault in my stored procedure not working. But while in the process of doing this my [dbo].[SpCategory_AddCategory] actually worked.

In conlusion i can not say why worked, it just did.

This is the main stored procedure that handles both inserting files into FileTable and inserting into Category table.

ALTER PROCEDURE [dbo].[SpCategory_AddCategory] 
     @CategoryName nvarchar(100),
     @Username nvarchar(100),
     @RelativeFilePath nvarchar(MAX),
     @FileName nvarchar(1000)
  
AS
BEGIN
    
    SET NOCOUNT ON;
    
    EXEC dbo.spLibraryTable_LoadFilesIntoFT @RelativeFilePath = @RelativeFilePath, @FileName = @FileName;

    ------------------------------------------------------------------------------------------------------------------

    DECLARE @stream_id uniqueidentifier;
    DECLARE @FileTableRoot varchar(1000);
    DECLARE @fullpath nvarchar(1000);   
    DECLARE @Id int;

    
    SELECT @FileTableRoot = FileTableRootPath();
    
    SELECT @fullpath = @FileTableRoot   ft.file_stream.GetFileNamespacePath()  
    FROM [dbo].LibraryTable  AS ft
    WHERE name = @FileName; 

    SET @stream_id = (SELECT stream_id FROM dbo.LibraryTable WHERE path_locator = GETPATHLOCATOR(@fullpath));
    
    EXEC dbo.spCategory_InsertIntoCategory @stream_id, @fullpath, @Username, @CategoryName, @Id = @Id OUTPUT
    
END

Here is dbo.spLibraryTable_LoadFilesIntoFT

ALTER PROCEDURE [dbo].[spLibraryTable_LoadFilesIntoFT] 
     @RelativeFilePath nvarchar(MAX),
     @FileName nvarchar(1000)
AS
BEGIN
    
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max),
     @attributes nvarchar(10),
     @tablename nvarchar(50),
     @filenameStr nvarchar(1000),
     @relativeFP nvarchar(MAX)
    
    SET @filenameStr = @FileName;
    SET @relativeFP = @RelativeFilePath;
    SET @attributes = N' * ';
    SET @tablename = N'FileData ';


    SET @sql = N'SELECT '   CHAR(39)   @filenameStr   CHAR(39)   N', '   @attributes   
               N' FROM '   N'OPENROWSET(BULK '   CHAR(39)  @relativeFP   CHAR(39)   ', SINGLE_BLOB)'   N' AS '   @tablename;
    
    INSERT INTO [dbo].[LibraryTable] ([name],[file_stream])
    EXEC(@sql);

END

Here is dbo.spCategory_InsertIntoCategory

ALTER PROCEDURE [dbo].[spCategory_InsertIntoCategory] 
    @stream_id uniqueidentifier,
    @fullpath nvarchar(1000),
    @Username nvarchar(100),
    @CategoryName nvarchar(100),
    @Id int = 0 output
AS
BEGIN
    
    SET NOCOUNT ON;

    
    INSERT INTO dbo.[Category] (categoryName, stream_id, userId)
    VALUES(@CategoryName, @stream_id , (SELECT id FROM dbo.[User] WHERE dbo.[User].username = @Username));
    
    Select @Id = SCOPE_IDENTITY();
END