How to Fix 'Incorrect syntax near '@filepath'' Error in SQL Server Bulk Insert Stored Procedure?

Bulk Insert through Stored Procedure: Resolving Error

In an attempt to encapsulate a working bulk insert query into a stored procedure, a user encountered errors when utilizing file variables within the BULK INSERT statement. The user's stored procedure code appears as follows:

create proc dbo.InsertZipCode
@filepath varchar(500)='e:-digit Commercial.csv'
as
begin
BULK INSERT ZIPCodes 
FROM  @filepath 
WITH 
( 
     FIRSTROW = 2 ,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)
end

However, this code resulted in the following errors:

Msg 102, Level 15, State 1, Procedure
InsertZipCode, Line 6 Incorrect syntax
near '@filepath'.

Msg 319, Level 15, State 1, Procedure
InsertZipCode, Line 7 Incorrect syntax
near the keyword 'with'. If this
statement is a common table
expression, an xmlnamespaces clause or
a change tracking context clause, the
previous statement must be terminated
with a semicolon.

The crux of this issue lies in the inability of BULK INSERT to accept file names as variables. While a statement like this is valid:

BULK INSERT ZIPCodes 
FROM  'e:-digit Commercial.csv' 
WITH 

Employing a file name variable (e.g., DECLARE @filename VARCHAR(255) SET @filename = 'e:5-digit Commercial.csv') within the BULK INSERT statement will inevitably fail.

To resolve this issue, one viable approach is to construct the BULK INSERT statement as a string (with a fixed file name) and execute it dynamically using sp_executesql:

DECLARE @filepath nvarchar(500)
SET @filepath = N'e:-digit Commercial.csv'

DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
       N'BULK INSERT ZIPCodes FROM ''' + 
       @filepath + 
       N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

EXEC sp_executesql @bulkinsert

By adhering to these guidelines, the inability of BULK INSERT to accept file name variables can be circumvented, allowing for the successful creation of stored procedures that execute bulk inserts.

The above is the detailed content of How to Fix 'Incorrect syntax near '@filepath'' Error in SQL Server Bulk Insert Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!