Post reply

I can't figure out what's wrong with this syntax

  • August 31, 2012 at 8:41 am

    #281055

    Hello,

    Error message that I am getting is 'Incorrect syntax near '+'.

    Select @Years = (2001,2002)

    INSERT INTO #freddata (ROUTE_NBR ,

    MILELOG ,

    VMT ,

    END_MEASURE ,

    RCLINK ,

    YEAR )

    SELECT route_number, beg_measure , AADT_TOTAL ,end_measure, RCLINK, YEAR FROM OPENQUERY(EDWGEARS,'SELECT route_number, beg_measure AS MILELOG,

    AADT_TOTAL AS VMT, end_measure, RCLINK, YEAR FROM VW_FRED_AADT_HIST

    WHERE RCLINK IS NOT NULL and YEAR IN ' + @Years + '') a

    INNER JOIN #gearsdata b ON a.RCLINK = b.RCLINK and a.YEAR = b.ACC_YEAR

  • August 31, 2012 at 8:50 am

    #1532440

    I think the issue is related to OPENQUERY (linky to BOL) ;

    OPENQUERY does not accept variables for its arguments.

    it requires a static string as the second parameter, so you cannot dynamically put together the string like that;

    you can't pass it a variable, like a varchar either.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 31, 2012 at 8:58 am

    #1532444

    Thanks Lowell.

    But this @Years(2001,2002,2005,2008) values are passed by the users thru stored proc and years can be different every time; in this case, do you think I can pass this variable to the openquery some other way?

    Thanks

  • August 31, 2012 at 9:00 am

    #1532446

    you could use the linked server instead of open query, or switch the whole openquery command to be dynamic SQL;

    that's all i can think of.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 31, 2012 at 9:13 am

    #1532463

    Ajdba (8/31/2012)

    Thanks Lowell.

    But this @Years(2001,2002,2005,2008) values are passed by the users thru stored proc and years can be different every time; in this case, do you think I can pass this variable to the openquery some other way?

    Thanks

    I'd be more worried about the problems with SQL Injection that you're creating here.

    If you show us the whole stored procedure, we can probably show you how to fix both problems.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • September 3, 2012 at 3:11 pm

    #1533066

    you could get the sp to create a #temptable from the string of years passed in and then use this in either the JOIN or WHERE criteria, depending on what you are trying to do.

    you will still need to make sure the incoming data is 'clean' from a SQL injection attack point of view.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply