Insert file on SQL Server without front end using stored procedure

Question

I am trying to insert a file through SQL. I am using the following query.

INSERT INTO [dbo].[Attachments] (FileName, FileBinary) SELECT 'non-date-in-sql-server-column', BulkColumn FROM OPENROWSET(Bulk 'C:\Users\Pictures\Picture.JPG', SINGLE_BLOB) AS BLOB 

It is working fine.

I want to write a procedure using a dynamic path. This gives me an error that I cannot use Filebinary in addin. What is a varatinary datatype. What is the best way?

I did the following, but did not take the binary value correctly.

 DECLARE @SQLString NVARCHAR(MAX) SET @SQLString = 'SELECT ' + '''' +@Filename +'''' + ' AS Name,' + 'FileBinary FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS FileBinary(FileBinary);' Insert Into Attachments (ApplicantID, FileName, FileBinary) Values (@ApplicantID, @FileName, Convert(varbinary(max), @SQLString)) 

Answer 1

Place the Insert statement inside the dynamic query and execute it.

Now your @SQLString will not have the FileBinary value, it will have a line with a dynamic layout. You need to execute it to get the values

 DECLARE @SQLString NVARCHAR(MAX), @Filename VARCHAR(500), -- Pass file name here @ApplicantID VARCHAR(500) --Pass Application ID here SET @SQLString = ' Insert Into Attachments ( ApplicantID, FileName, FileBinary ) SELECT @ApplicantID,@Filename,FileBinary FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS FileBinary(FileBinary);' EXEC Sp_executesql @SQLString, N'@Filename varchar(500),@ApplicantID varchar(500)', @Filename =@Filename , @ ApplicantID=@ApplicantID