Unable to bulk insert from flat file in SQL

If I execute the script below, the data can be inserted into the table.

CREATE PROCEDURE dbo.loadDataFrFlatFile

AS
BEGIN


BULK INSERT PERSONS
FROM 'C:\SampleData1.csv'
WITH (FieldTerminator = '|', RowTerminator = '\n')
END

RETURN 1

GO

EXEC dbo.loadDataFrFlatFile

However, if I put the file path as an input variable, it doesn't work.

CREATE PROCEDURE dbo.loadDataFrFlatFile
(
    @flatFilePath varchar(255)
)

AS
BEGIN    

BULK INSERT PERSONS
FROM ' + @flatFilePath + '
WITH (FieldTerminator = '|', RowTerminator = '\n')
END

RETURN 1

GO

EXEC dbo.loadDataFrFlatFile @flatFilePath = 'C:\SampleData1.csv'

The error shown is:

Msg 4860, Level 16, State 1, Procedure loadDataFrFlatFile, Line 12
Cannot bulk load. The file " + @flatFilePath + " does not exist.

Please help. Thanks in advance.

Solution

You would need to use dynamic sql for that, e.g.:

create procedure dbo.loadDataFrFlatFile ( @flatFilePath varchar(255) ) as
begin;
declare @sql nvarchar(max) = 'bulk insert persons
from ''' + @flatFilePath + '''
with (FieldTerminator = ''|'', RowTerminator = ''\n'')
';
exec sp_executesql @sql;
return 1
end;
go

exec dbo.loadDataFrFlatFile @flatFilePath = 'C:\SampleData1.csv'

Be careful with this regarding sql injection, make sure only those who need to use it have the permission to execute it.

You can also white-list and blacklist the values the variable can contain prior to concatenating and executing it.

dynamic sql reference: