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