Sql-server – Incorrect Syntax With Dynamic SQL

sql serversql-server-2008-r2t-sql

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''')

Best Answer

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
Related Question