Using Dynamic SQL for a Bulk Insert in a Stored Procedure

Question

I have a .NET App from which I am calling 2 stored procedures. One of these stored procedures is a Bulk Insert, to which I will pass in the appropriate filename, table name, and delimiter from the .NET app. After some research, I found out that I would need to use dynamic SQL to allow for variable filenames in a BULK INSERT.

The code I have now reads:

CREATE PROCEDURE transfer_data 
    @file_path VARCHAR, @t_name VARCHAR, @delimeter VARCHAR
AS
    BULK INSERT @t_name
    FROM @file_path
    WITH (
             FIELDTERMINATOR = @delimeter,
             ROWTERMINATOR = '\n'
         );

How would I refactor this? Other examples that I've seen (BULK INSERT with variable file name) are still setting the variable in the query, however I will be passing the parameters from my .NET App.

You cannot replace literals with Variables. You will have to inject the values **safely**, and you'll need variables that are longer than 1 character in length. — Thom A, Mar 31 '20 at 13:26
you are referring to the variable @delimeter, right? In that case, I'll create a seperate stored proc for the different cases I need for delimetes. Also, I am injecting the values safely I believe, I am using the SqlCommand and SqlParameter in my .NET App — , Mar 31 '20 at 13:30
@fragElephant, Larnu is referring to the parameter declarations in the stored procedure. All of the parameters are declared as `VARCHAR(n)` without a value for `(n)`. [When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30.](https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-ver15) So all of your parameters are currently limited to one byte. — Eric Brandt, Mar 31 '20 at 13:44

score 1 · Accepted Answer · answered Mar 31 '20 at 13:34

You need to safely inject the values. You can't replace literals with variables. Due to the File path having the chance of being longer than 128 characters, i use REPLACE rather than QUOTENAME. I also assume that the delimiter is only 1 character in length:

CREATE PROCEDURE dbo.transfer_data @file_path nvarchar(255), @s_name sysname = N'dbo', @t_name sysname, @delimiter nchar(1) AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'BULK INSERT ' + QUOTENAME(@s_name) + N'.' + QUOTENAME(@t_name) + @CRLF +
               N'FROM N''' + REPLACE(@file_path,'''','''''') + N'''' + @CRLF + 
               N'WITH (FIELDTERMINATOR = N' + QUOTENAME(@delimiter,'''') + N',' + @CRLF + 
               N'      ROWTERMINATOR = ''\n'');'

    --PRINT @SQL;
    EXEC sys.sp_executesql @SQL;
END;

Carriage Return, Line Feed, @fragElephant . – Thom A Mar 31 '20 at 13:42 — Thom A, Mar 31 '20 at 13:42

Using Dynamic SQL for a Bulk Insert in a Stored Procedure

1 Answers1