Sql-server – How to specify the path to a file dynamically in OPENROWSET(BULK…)

bulkinsertsql-servertsql

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?

Best Solution

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.