0

I am trying to run the below statement, but I keep getting an error of

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '+'.

What do I change so this becomes valid syntax?

    Declare @salesman varchar(max) = 'Rich'

Select * FROM OPENQUERY(XXX.XXX.XXX.X,'Select 
        cm.salesman salesmanName
        ,iv.saledate InvoiceDate
        ,CAST(COALESCE(jc.salecost,0) AS DOUBLE PRECISION) As totalsalecost
        ,jc.saleid saleid
FROM jobcost jc
        INNER JOIN arc ac
        ON jc.jcid = ac.jcid
        LEFT OUTER JOIN rama wp
        ON wp.jwid = ac.jwid
        Left join ira iv
        ON jc.saleid = iv.saleid
        LEFT JOIN sales jb
        ON jc.saleid = jb.saleid
        INNER JOIN salesman cm
        ON cm.arid = jb.arid
        WHERE wp.jwid IN (1,3,4,5,6) AND (ac.includeascost)
        AND jc.saleid IN (Select saleid from ira)
               AND cm.salesman IN ('+@salesman+')
               AND iv.saledate BETWEEN ''01/01/2016'' AND ''12/31/2016''')

1 Answer 1

2

You can't, the query portion of the OPENQUERY must be a string literal, so no variables passed in. You can make the whole thing dynamic SQL though.

 Declare @salesman varchar(max) = 'Rich'

 DECLARE @Query NVARCHAR(4000) = '

Select * FROM OPENQUERY(XXX.XXX.XXX.X,''Select 
        cm.salesman salesmanName
        ,iv.saledate InvoiceDate
        ,CAST(COALESCE(jc.salecost,0) AS DOUBLE PRECISION) As totalsalecost
        ,jc.saleid saleid
FROM jobcost jc
        INNER JOIN arc ac
        ON jc.jcid = ac.jcid
        LEFT OUTER JOIN rama wp
        ON wp.jwid = ac.jwid
        Left join ira iv
        ON jc.saleid = iv.saleid
        LEFT JOIN sales jb
        ON jc.saleid = jb.saleid
        INNER JOIN salesman cm
        ON cm.arid = jb.arid
        WHERE wp.jwid IN (1,3,4,5,6) AND (ac.includeascost)
        AND jc.saleid IN (Select saleid from ira)
               AND cm.salesman IN ('+@salesman+')
               AND iv.saledate BETWEEN ' + QUOTENAME('01/01/2016', '''') + ' AND ' + QUOTENAME('12/31/2016', '''') + ')'

EXEC sp_ExecuteSQL @Query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.