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?