Inserting a file into table in sql server using Stored Procedure Giving error

Question

declare  @path nvarchar(max)

set @path='C:\a\SQLQuery1.sql'

SELECT * FROM OPENROWSET(BULK '@path',SINGLE_BLOB) as res

I am trying to execute this particular block in SQL server but its throwing error saying that

Can not bulk load ,@path does not exist.

But If I Provide the path explicitly in the last line instead of @path then its successfully running. Any help!!

Answer 1

You can try using dynamic SQL:

DECLARE @path nvarchar(max), @sql nvarchar(max);

SET @path = N'C:\a\SQLQuery1.sql';
SET @sql = N'SELECT * FROM OPENROWSET(BULK '''+@path+''',SINGLE_BLOB) as res';

EXEC(@sql);