OPENROWSET won't accept a variable?

Question

SQL Server 2008 R2

This doesn't work:

IF @exists = 1 
    SELECT
        @pic = pImage.BulkColumn
    FROM
        OPENROWSET(BULK @fn, SINGLE_BLOB) AS pImage
ELSE
    SET @pic = NULL

unless I replace @fn (an NVARCHAR) with a hard coded string like N'C:\path\to\some.jpg'. But there must be a way to use a variable there, right?

score 0 · Answer 1 · edited May 23 '17 at 10:29

I would suggest using Dynamic sql, something like in this answer: Using a Variable in OPENROWSET Query

On your case it would be something like:

DECLARE @sql VARCHAR(MAX)
IF @exists = 1 
SET @sql = 'SELECT pImage.BulkColumn FROM OPENROWSET(BULK ''' + @fn + ''', SINGLE_BLOB) AS pImage'

sp_execute @sql
--Here you would need to add something to retreive @pic (I didn't add it since I don't know what @pic is. You can guide you with this link https://msdn.microsoft.com/en-us/library/ms188001.aspx
ELSE 
SET @pic = NULL

OPENROWSET won't accept a variable?

1 Answers1