How to add a dynamic URL inside an OPENROW SET

Question

I have a set of data rows in my Documents table, Each row in this table has a unique column AbsoluteUri (which is hosted in Azure Container). What I want to do is to add a dynamic url inside the BULK of the OPENROWSET in order to achieve my goal which is to store the response (VARBINARY(MAX)). When I try to put a static single qoute in the BULK it works but when I try to make it dynamic by adding data from the TDM.AbsoluteUri it throws some error which is invalid syntax. Below is that I have tried.

SELECT TR.Id AS TransactionID, TDM.Id AS DocumentID,
(SELECT *
FROM OPENROWSET
(
BULK TDM.AbsoluteUri,
DATA_SOURCE = 'DocumentsUri',
SINGLE_BLOB
) AS blob),
FROM [dbo].[Transaction] AS TR
LEFT JOIN [dbo].[Documents] AS TDM ON TR.Id = TDM.TransactionId

Expected results should be from the DocumentsUri into a VARBINARY(MAX) data.

Answer 1

The file name must be a string literal, which means dynamic SQL and a cursor. EG

declare @images table(name varchar(200), filename varchar(200))
insert into @images (name, filename) values ('Hammer','public/Hammer.jpg')
insert into @images (name, filename) values ('Screwdriver','public/screwdriver.jpg')

declare @localImages table(name varchar(200), image varbinary(max))

declare @name varchar(200), @filename varchar(200)

declare c cursor local for 
    select * from @images
open c

fetch next from c into @name, @filename

while @@FETCH_STATUS = 0
begin
  
  declare @sql nvarchar(max) = concat(N'
    SELECT ''',@name,''' name,  BulkColumn Image 
    FROM OPENROWSET
    (
       BULK ''',@filename, ''',
       DATA_SOURCE = ''BlobStore'',
       SINGLE_BLOB
    ) AS blob;  
     ');

     print @sql

    insert into @localImages(name,image)
    exec (@sql)

  fetch next from c into @name, @filename
end

select * 
from @localImages