How can I specify the path to a file dynamically in OPENROWSET(BULK...)?

Question

I want to insert images into an Image field, preferably using a stored procedure which will accept a path to an image. After hacking around I came up with this;

-- functional
DECLARE @parameters nvarchar(max) = '';
DECLARE @sql_string nvarchar(max) = 
N'UPDATE MyTable
  SET MyImageField = (SELECT BulkColumn 
                      FROM Openrowset(Bulk ''' + @PathToMyImage + ''', Single_Blob) ImageData)
  WHERE MyPrimaryKey = ' + CAST(@PrimaryKey AS NVARCHAR(max));

EXECUTE sp_executesql @sql_string,  @parameters

I did this because when I tried;

--Not functional
INSERT INTO MyTable (MyImageField) 
VALUES ((SELECT BulkColumn 
         FROM Openrowset(Bulk @PathToMyImage, Single_Blob) ImageData));

SQL Server throws an error, complaining that Bulk expects a string. I'd prefer to not have to resort to sp_executesql for maintainability / readability, is there a better way to go about this?

Answer 1

You do have to use dynamic sql but you can use quotename() to avoid embedded quote nightmares and you should pass the primary key in as an actual parameter.

DECLARE @sql_string nvarchar(max) = 
N'UPDATE MyTable
  SET MyImageField = (SELECT BulkColumn 
                      FROM Openrowset(Bulk ' + quotename(@PathToMyImage,nchar(39)) + ', Single_Blob) ImageData)
  WHERE MyPrimaryKey = @PrimaryKey';

EXECUTE sp_executesql @sql_string, N'@PrimaryKey int',  @PrimaryKey

nchar(39) is a single quote. All single quotes within the path name will be properly escaped and the path will be enclosed in single quotes for safe concatenation.