Why is dynamic SQL not working with bulk insert?

Question

I need to incorporate variables instead of hard-coded file name in bulk insert query so that file name is retrieved through input parameter of a stored procedure.

I tried the following code:

DECLARE       @sql   VARCHAR(MAX);
DECLARE       @fileDate     VARCHAR(100);
DECLARE       @filePath     VARCHAR(100);

SET @fileDate = CAST(GETDATE() AS date);
SET @filePath = 'D:\BI-SCRIPTS\Nauman\test.csv';

SET @sql = 
        N'BULK INSERT MMBL_phase2.dbo.auto_test' + 'FROM ''' + @filePath + '''' +
        ' WITH(
            FIRSTROW = 2,
            FIELDTERMINATOR = '','',
            ROWTERMINATOR = ''0x0a''
        );'

        EXEC (@sql);

But it gives the following error:

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near 'D:\BI-SCRIPTS\Nauman\test.csv'.

What am I doing wrong?

Answer 1

Your code has been modified Missing spaces in dynamically generating query.

DECLARE       @sql   VARCHAR(MAX);
DECLARE       @fileDate     VARCHAR(100);
DECLARE       @filePath     VARCHAR(100);

SET @fileDate = CAST(GETDATE() AS date);
SET @filePath = 'D:\BI-SCRIPTS\Nauman\test.csv';

SET @sql = 
    N'BULK INSERT MMBL_phase2.dbo.auto_test' + ' FROM ''' + @filePath + '''' +
    ' WITH(
        FIRSTROW = 2,
        FIELDTERMINATOR = '','',
        ROWTERMINATOR = ''0x0a''
    );'

    EXEC (@sql);