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?