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?

CodePudding user response：

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'