How to cast variables in T-SQL for bulk insert?

Question

The following code gives an error (its part of a T-SQL stored procedure):

-- Bulk insert data from the .csv file into the staging table.
DECLARE @CSVfile nvarchar(255);
SET @CSVfile = N'T:\x.csv';
BULK INSERT [dbo].[TStagingTable]
-- FROM N'T:\x.csv' -- This line works
FROM @CSVfile -- This line will not work
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2    
)

The error is:

Incorrect syntax near the keyword 'with'. 

If I replace:

FROM @CSVfile

with:

FROM 'T:\x.csv'

... then it works nicely.

Answer 1

As I know only literal string is required in the from. In that case you have to write a dynamic query to use bulk insert

declare @q nvarchar(MAX);
set @q=
    'BULK INSERT [TStagingTable]
    FROM '+char(39)+@CSVfile+char(39)+'
    WITH
    (
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n'',
    FIRSTROW = 1  
    )'
exec(@q)

Answer 2

Have you tried with dynamic SQL?

SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "

and then

EXEC(@SQL)

Ref.: http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file