Stored Procedure Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure Query

Author

Topic

Aeolos
Starting Member

1 Post

Posted - 2012-01-21 : 09:13:00

Hi, I have wrote a simple procedure to write the contents of a CSV file to a Table. My problem is if I specify the file location the procedure saves and works fine. If I replace the file name with a parameter I get an error when saving the procedure:

Incorrect syntax near '@Param1'.
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.

Working Procedure that will save (filepath specified)
CREATE PROCEDURE TestProca
(
@Param1 varchar(50) = NULL
)
AS
BULK
INSERT CSVImport
FROM 'd:\csvtest.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Working Procedure that will save (filepath as parameter)
CREATE PROCEDURE TestProca
(
@Param1 varchar(50) = NULL
)
AS
BULK
INSERT CSVImport
FROM @Param1
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Can anyone advise, thanks in advance
Aeolos

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-21 : 10:46:52

filepath can't be a variable.
I found this workaround:
http://stackoverflow.com/questions/4050790/bulk-insert-using-stored-procedure

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources