SQL OPENQUERY, Incorrect syntax near '+'

Question

I have this sql query:

DECLARE  @VAR varchar(8)
SET  @VAR = '20140202'
SELECT * FROM OPENQUERY([NMD],'SELECT * FROM NmdItemGroups01.dbo.InvTranDtl WHERE TranDate = ''' + @VAR + '''')

But when I run it, it gives this error:

Incorrect syntax near '+'

`OPENQUERY()` does not support expression in the parameter. You will need to use `Dynamic SQL` if you need to do that — Squirrel, Jun 03 '19 at 05:10
But how the queries in this link work: https://support.microsoft.com/en-us/help/314520/how-to-pass-a-variable-to-a-linked-server-query — amin mohammadi, Jun 03 '19 at 05:13

Max Szczurek · Accepted Answer · 2019-06-03T05:30:55.873

2

sp_executesql example:

DECLARE  @VAR varchar(8)
SET  @VAR = '20140202'

EXEC sp_executesql 
    N'SELECT * FROM NmdItemGroups01.dbo.InvTranDtl WHERE TranDate = @TranDate', 
    N'@TranDate DATE', 
    @VAR

edited Jun 03 '19 at 05:30

answered Jun 03 '19 at 05:24

Max Szczurek

4,324
2
20
32

it is indeed `Dynamic SQL` – Squirrel Jun 03 '19 at 05:52

SQL OPENQUERY, Incorrect syntax near '+'

1 Answers1