How to use a variable in Openrowset command
<p>I am trying to use a variable filepath in a SQL Openrowset command. I'm aware that it can't explicitly accept a variable and that I need to make use of dynamic SQL.</p>

<p>What currently works - </p>

<pre><code>SELECT @file_stream = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\Temp\print4.pdf', SINGLE_BLOB) AS x
</code></pre>

<p>However if I try to use my variable filepath</p>

<pre><code>declare @file_stream VARBINARY(MAX)

declare @filePath NVARCHAR(128)
set @filePath = 'C:\Temp\print4.pdf'

set @command = N'SELECT @file_stream = CAST(bulkcolumn AS varbinary(MAX))
                from OPENROWSET(BULK ' + @filePath + ',
                SINGLE_BLOB) ROW_SET'

EXEC sp_executesql @command, @filePath, @file_stream;
</code></pre>

<p>I get the error 'Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@filePath".'</p>

<p>I'm sure this is an issue of syntax but haven't been able to figure out how it should be formatted yet.</p>