0

Hi I am having problem with the stored procedure that I created. I got a syntax error with Incorrect syntax near '@path'.

If I put the absolute path 'J:\TestCSV\ImportData.csv' I don't received any error. But if I declare parameters, I received this error that I mentioned above. Here's my stored procedure. Hoping for your helping hand.

Create PROCEDURE [dbo].[FileUpload]

 As
BEGIN


DECLARE @path VARCHAR(256); -- path   
DECLARE @fileName VARCHAR(256); -- filename   
DECLARE @fileDate VARCHAR(20); -- used for file date
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) ;
SET @fileName = 'ImportData' + '_' + @fileDate + '.csv';
SET @path = 'J:\TestCSV\' + @fileName;

BULK

INSERT [dbo].[temp_Cemetery]
--FROM 'E:\CSVTest\ImportDataYYYYMMDD.csv'
FROM @path  -- Error Here Incorrect syntax near '@path'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


END
Jake Marmak
  • 65
  • 6
  • Possible duplicate of [Bulk Insert with filename parameter](http://stackoverflow.com/questions/7306616/bulk-insert-with-filename-parameter) – Tab Alleman Jan 04 '17 at 16:10

3 Answers3

1

You have to use a string literal for some reason. You'll probably have to use dynamic sql e.g. 

CREATE PROCEDURE [dbo].[FileUpload]
AS
BEGIN


DECLARE @path VARCHAR(256); -- path   
DECLARE @fileName VARCHAR(256); -- filename   
DECLARE @fileDate VARCHAR(20); -- used for file date
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) ;
SET @fileName = 'ImportData' + '_' + @fileDate + '.csv';
SET @path = 'J:\\TestCSV\\' + @fileName;
DECLARE @sql varchar(8000)

SET @sql = 'BULK INSERT [dbo].[temp_Cemetery]
FROM ''' + @path + '''
WITH
(
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n''
)'

EXEC (@sql)

END
James Casey
  • 2,447
  • 1
  • 11
  • 19
0

The issue is you needed to escape the backslash near the quote:

SET @path = 'J:\\TestCSV\\' + @fileName;

This is because the backslash is a special character and since you escaped the single quote it treated the rest of the statement as part of the string. I fixed it for you below.

Create PROCEDURE [dbo].[FileUpload]

 As
BEGIN


DECLARE @path VARCHAR(256); -- path   
DECLARE @fileName VARCHAR(256); -- filename   
DECLARE @fileDate VARCHAR(20); -- used for file date
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) ;
SET @fileName = 'ImportData' + '_' + @fileDate + '.csv';
SET @path = 'J:\\TestCSV\\' + @fileName;

BULK

INSERT [dbo].[temp_Cemetery]
--FROM 'E:\CSVTest\ImportDataYYYYMMDD.csv'
FROM @path  -- Error Here Incorrect syntax near '@path'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


END
Jonathan Porter
  • 1,365
  • 7
  • 34
  • 62
  • 1
    Still having same error.Incorrect syntax near '@path'. Msg .319, Level 15, State 1, Procedure MPGUpload, Line 25 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 – Jake Marmak Jan 04 '17 at 16:06
0

You need to add quotes to the path so that your FROM clause reads it as 'J:\TestCSV\ImportData_20170104.csv' instead of J:\TestCSV\ImportData_20170104.csv

Create PROCEDURE [dbo].[FileUpload]

 As
BEGIN


DECLARE @path VARCHAR(256); -- path   
DECLARE @fileName VARCHAR(256); -- filename   
DECLARE @fileDate VARCHAR(20); -- used for file date
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) ;
SET @fileName = 'ImportData' + '_' + @fileDate + '.csv';
SET @path = '''J:\TestCSV\' + @fileName +'''';



BULK
INSERT [dbo].[temp_Cemetery]
--FROM 'E:\CSVTest\ImportDataYYYYMMDD.csv'
FROM @path  -- Error Here Incorrect syntax near '@path'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


END
S3S
  • 24,809
  • 5
  • 26
  • 45