How to correctly use dynamic SQL with bulk insert?

Question

I have a stored procedure like:

CREATE PROCEDURE [dbo].[sp_writeBulk] 
 @usersID NVARCHAR(30)
 ,@fileName NVARCHAR(50)
 ,@nameMeasuringPoint NVARCHAR(30)
 ,@filesID NVARCHAR(30)
 ,@filePath NVARCHAR(1000)
AS
BEGIN
 SET NOCOUNT ON;
 BEGIN TRANSACTION
 DECLARE
 @timestamp DATETIME2(3) = SYSDATETIME()
 ,@nameMeasuringPointID INT
 ,@sql_select NVARCHAR(1000)
 ,@sql_insert NVARCHAR(1000)
 IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL
 BEGIN
 DROP TABLE #tmp
 END
 CREATE TABLE #tmp
 (
 A VARCHAR(30)
 ,B VARCHAR(30)
 ,C VARCHAR(30)
 )
 SET @sql_insert = N'BULK INSERT #tmp
 FROM @filePath
 WITH
 (
 FIELDTERMINATOR = '';'',
 ROWTERMINATOR = ''\n'',
 ROWS_PER_BATCH = 10000, 
 TABLOCK
 )'
 EXEC sp_executesql @sql_insert,
 N'@filePath NVARCHAR(1000)', @filePath;
 -- get @nameMeasuringPointID 
 SET @sql_select = N'SELECT @nameMeasuringPointID = mm.MeasuringPointID FROM dbo.MeasuringPoints AS mm
 WHERE mm.LocationName = @nameMeasuringPoint'
 EXEC sp_executesql @sql_select, 
 N'@nameMeasuringPoint NVARCHAR(30), @nameMeasuringPointID INT OUTPUT', @nameMeasuringPoint, @nameMeasuringPointID OUTPUT;
 
 INSERT INTO dbo.Readings(UsersID, FilesID, A, B, C, FileName, XDateTime, NameMeasuringPointID)
 SELECT @usersID, @filesID, A, B, C, @fileName, @timestamp, @nameMeasuringPointID 
 FROM #tmp;
 
 DROP TABLE #tmp;
 IF @@ERROR <> 0
 BEGIN
 GOTO error;
 END;
 COMMIT TRANSACTION;
 RETURN 0;
 error:
 IF @@TRANCOUNT > 0
 BEGIN
 ROLLBACK TRANSACTION;
 END;
 RETURN 1;
END;
GO
GRANT EXEC ON [dbo].[sp_writeBulk] TO myuser;
GO

When I execute it like:

exec sp_writeBulk '2', 'name_test', 'MP Test', '1', 'C:\Users\myuser\Downloads\myTest.csv'

I get:

Incorrect syntax near '@filePath'.

I can't get what is wrong with it?

• Charlieface: Why is @sql_select dynamic, it seems unnecessary. Side point: I'd suggest you scrap that horrible error handling and either replace with TRY CATCH, or better: don't handle errors at all, just use SET XACT_ABORT ON; to ensure the transaction is rolled back
• Hrvoje T: Thank you, all your suggestions have been adopted

Answer 1

Unfortunately, you cannot parameterize a BULK INSERT statement. Instead you will have to safely inject it, using QUOTENAME

 SET @sql_insert = N'BULK INSERT #tmp
 FROM ' + QUOTENAME(@filePath, '''') + N'
 WITH
 (
 FIELDTERMINATOR = '';'',
 ROWTERMINATOR = ''\n'',
 ROWS_PER_BATCH = 10000, 
 TABLOCK
 )';
 EXEC sp_executesql @sql_insert;

---

The grammar for BULK INSERT shows the file name must be a literal in quotes:

BULK INSERT
 { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
 FROM 'data_file'