CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Werner Reyneke
Werner Reyneke

Reputation: 3

SQL server create stored procedure syntax error

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?

Upvotes: 0

Views: 222

Answers (2)

DavidG
DavidG

Reputation: 118937

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.

Upvotes: 2

jazza1000
jazza1000

Reputation: 4247

@filepath is int, you probably want something like 

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

Upvotes: 0

Related Questions