SQL Server SP 'Incorrect syntax' error using variable

Question

I have a bunch of CSV files to import and so I've created BCPFORMAT files in XML. These are all working well and I can import the data correctly.

I'm now trying to create a stored procedure (which I've never done before) that accepts three parameters:
1. The table name
2. The path to the CSV file
3. The path to the BCPFORMAT file

CREATE PROC dbo.usp_import_csv 
    @table_name nvarchar(100),      /* name of table to import into */
    @csv_path nvarchar(1000),       /* path to csv file */
    @bcp_format_path nvarchar(1000) /* path to BCPFORMAT file */
AS
BEGIN
    DELETE FROM @table_name
    BULK INSERT @table_name
        FROM  @csv_path
        WITH (FIRSTROW = 1 , FORMATFILE = @bcp_format_path)

END

EXEC dbo.usp_import_cttp 'dbo.new_table', 'C:\temp\new_table.csv', 'C:\Temp\new_table.xml'

I get the message Incorrect syntax near '@table_name'. so I'm obviously not doing something quite right. But I can't figure out what that is...

ANSWER DERIVED FROM BELOW

CREATE PROC dbo.usp_import_cttp
    @table_name nvarchar(100),      /* table to import into */ 
    @csv_path nvarchar(1000),       /* folder containing latest NZULM data files */
    @bcp_format_path nvarchar(1000) /* folder containing BCPFORMAT files used to explain csv files */
AS
BEGIN
    EXEC sp_executesql 'DELETE FROM @table_name', @table_name;

    EXEC sp_executesql 'BULK INSERT @table_name
                        FROM  @csv_path
                        WITH (FIRSTROW = 1 , FORMATFILE = @bcp_format_path)', 
                        @table_name, 
                        @csv_path,
                        @bcp_format_path;

END

Your update does not work. should be 'BULK INSERT ' + quotename(@table_name) + ' FROM @csv_path ...' — Filip De Vos
– Filip De Vos, Commented Mar 21, 2012 at 0:08
More likely to need proper parameter declarations. In any case yes, @Filip is right, the "answer" appended to the question will certainly not work as is. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 21, 2012 at 0:33

Joel Coehoorn · Accepted Answer · 2012-03-20 23:51:47Z

1

You can't put table names into variables. If you want to do that, your only resort is dynamic sql: building the query in a string variable. The good news is that you can at least still use sp_executesql for the other parameters.

For security purposes, I like to also run a query against the information_schema to make sure the supplied table name is valid.

answered Mar 20, 2012 at 23:51

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Derek Tomes Over a year ago

Does the same restriction apply to the other two variables? FROM @csv_path WITH (FIRSTROW = 1 , FORMATFILE = @bcp_format_path)

Collectives™ on Stack Overflow

SQL Server SP 'Incorrect syntax' error using variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related