SQL server create stored procedure syntax error

Question

I am trying to create a simple stored procedure:

CREATE PROCEDURE SP_Test @FilePath int
AS
    SELECT
       LastName, FirstName
    INTO 
       tmp_tblPerson
    FROM 
       OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (.txt, .csv)}','SELECT * FROM ' + @FilePath + "'")
GO

But I get a syntax error which I don't understand..?

Msg 102, Level 15, State 1, Procedure SP_Test, Line 12
Incorrect syntax near '+'.

Any ideas?

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Apr 14, 2015 at 9:10
Oh yes, thanks for the reminder, however I just named the sp quick and dirty for testing purposes. — Werner Reyneke
– Werner Reyneke, Commented Apr 15, 2015 at 12:58

DavidG · Accepted Answer · 2015-04-14 09:21:15Z

2

You can't use dynamic SQL when using using OPENROWSET. A workaround is to make the entire block use dynamically created SQL like this:

CREATE PROCEDURE SP_Test @FilePath int
AS
    DECLARE @sql NVARCHAR(MAX) = 
        'SELECT LastName, FirstName 
        INTO tmp_tblPerson 
        FROM OPENROWSET(
            ''MSDASQL'',
            ''Driver={Microsoft Access Text Driver (.txt, .csv)}'', 
            ''SELECT * FROM '' + @FilePath)'

    EXEC(@sql)

As always with dynamic SQL, make sure you are not vulnerable to SQL injection attacks.

Additionally, your query appears to be incorrect as I doubt you have a table with an integer as a name.

answered Apr 14, 2015 at 9:21

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

1 Comment

Werner Reyneke Over a year ago

Great thanks. This will work. Oh and ummm the int datatype is my own typo..hehe...it is meant to be a VARCHAR

jazza1000 · Accepted Answer · 2015-04-14 09:13:12Z

0

@filepath is int, you probably want something like

'SELECT * FROM ' + convert(varchar,@FilePath)

answered Apr 14, 2015 at 9:13

jazza1000

4,2773 gold badges48 silver badges71 bronze badges

1 Comment

marc_s Over a year ago

Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use

Collectives™ on Stack Overflow

SQL server create stored procedure syntax error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related