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?

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 — Charlieface
– Charlieface, Commented Dec 26, 2021 at 0:44

Charlieface · Accepted Answer · 2021-12-26 00:41:47Z

6

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'

answered Dec 26, 2021 at 0:41

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to correctly use dynamic SQL with bulk insert?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related