T-SQL: issue with string concat

Question

I have a set of audio files with names GreenLine1.mp3, GreenLine2.mp3 e.t.c. I'm going to write them into a table as BLOB (I use MS SQL Server'08), here's my sql request:

DECLARE @aud AS VARBINARY(MAX)
DECLARE @num AS INT    
-- Load the audio data
SET @num=1
WHILE (@num<38)
BEGIN;

SELECT @aud = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            'C:\Users\Ilya\folder\GreenLine' + CAST(@num AS VARCHAR) + '.mp3',
            SINGLE_BLOB ) AS x

-- Insert the data to the table          
INSERT INTO Mb2.dbo.Audios (Id, [Content])
SELECT NEWID(), @aud
SET @num = @num + 1
END;

I have an error: Incorrect syntax near '+', expecting ',' or ')'.

If I try to write

'C:\Users\Ilya\folder\GreenLine' + CAST(@num AS VARCHAR) + '.mp3'

into a variable and put it after BULK, I get Incorrect syntax near @variable, expected STRING, or TEXT_LEX

Answer 1

You can't parametrise or concatenate the parameters of OPENROWSET. It is constant values only.

You'll have to use dynamic SQL and a temp table, or consider using SSIS for example

Answer 2

This article pointed me in the right direction when I had the same issue with OPENQUERY:

https://web.archive.org/web/20120724073530/http://consultingblogs.emc.com/jamespipe/archive/2007/06/28/SQL-Server-2005_3A00_-Passing-variables-into-an-OPENQUERY-argument.aspx

Basically, you can wrap the entire statement in a variable (nvarchar), including the openrowset, and run exec sp_executesql @sql. It gets a little ugly to read around the 's though, because you'll have to escape them with ''.