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.

• Thom A: 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.
• user12955946: 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
• Eric Brandt: @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. So all of your parameters are currently limited to one byte.

Answer 1

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;

• user12955946: what is the @CRLF doing?
• Thom A: Carriage Return, Line Feed, @fragElephant .