15

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.

What currently works - 

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

However if I try to use my variable filepath

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;

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

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

bjjrolls
  • 529
  • 4
  • 8
  • 21
  • Check this [link](http://stackoverflow.com/questions/13831472/using-a-variable-in-openrowset-query) Maybe help you. – starko Oct 03 '16 at 09:09
  • @starko I was just looking at that question actually. It is slightly helpful but doesn't seem to deal with my issue directly. I've tried including the variable in double quotes like in the question to no avail. – bjjrolls Oct 03 '16 at 09:19

1 Answers1

20

Change your script like below.

DECLARE @file_stream VARBINARY(MAX)
DECLARE @command nvarchar(1000)
DECLARE @filePath NVARCHAR(128)
set @filePath = 'C:\Temp\print4.pdf'

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

EXEC sp_executesql @command, N'@file_stream1 VARBINARY(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT

select @file_stream

Sample Output : enter image description here

Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • Could you explain a little what is happening in the EXEC line? I have changed my script to this but am still seeing the error message... Must declare the scalar variable "@file_stream". – bjjrolls Oct 03 '16 at 10:41
  • i think you haven't change the variable used inside the dynamic query. change it to file_stream1 . Also makesure the variable is declared int the execute command.In the execute line, the result of the dynamic query is taken in to the output variable file_stream1 for getting it out. – Unnikrishnan R Oct 03 '16 at 10:56