title: How to use a variable in Openrowset command
body: <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>