How to use a variable in Openrowset command

Question

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.

@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
– bjjrolls, Commented Oct 3, 2016 at 9:19

Unnikrishnan R · Accepted Answer · 2016-10-03 10:58:33Z

23

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 :

edited Oct 3, 2016 at 10:58

answered Oct 3, 2016 at 9:42

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bjjrolls Over a year ago

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".

Unnikrishnan R Over a year ago

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.

Collectives™ on Stack Overflow

How to use a variable in Openrowset command

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related