Adding a parameter to a pass through query

Question

I am trying to use parameters within a pass through query the issue is when I set the statement up I get

Incorrect syntax near '+'. Expecting ')'

I've done this before so I am not sure what I am doing incorrectly now. I've shortened the query below to give the gist.

DECLARE @WORK_DATE AS VARCHAR(20)

SET @WORK_DATE = '20160210'

SELECT * FROM OPENQUERY(DATA_BASE_1,
  'SELECT EMPLOYEE.EMPLOYEE_NUMBER AS EMPLOYEE_ID
  FROM TABLE_1 EMPLOYEE
  LEFT JOIN TABLE_2 PERSON
  ON EMPLOYEE.EMPLOYEE_NUMBER = PERSON.EMPLOYEE_NUMBER
  AND PERSON.WORK_DATE = '''''+ @WORK_DATE + '''''')

You can't use variables in `OPENQUERY`. Try using dynamic SQL. e.g. `DECLARE @WORK_DATE AS VARCHAR(20) SET @WORK_DATE = '2002-11-21 00:00:00' DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM OPENQUERY(Database1, ''SELECT EMPLOYEE.EMPLOYEE_NUMBER AS EMPLOYEE_ID FROM TABLE_1 EMPLOYEE LEFT JOIN TABLE_2 PERSON ON EMPLOYEE.EMPLOYEE_NUMBER = PERSON.EMPLOYEE_NUMBER AND PERSON.WORK_DATE =''''' + @WORK_DATE + ''''''')' EXEC(@SQL)` — ZLK, Feb 21 '16 at 21:33

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

From Transact-SQL OPENQUERY documentation:

Remarks

OPENQUERY does not accept variables for its arguments.

One way to do this would be to pass the variable to a string storing the query to be executed and then execute it.

DECLARE @SQL_TO_EXEC NVARCHAR(MAX), @WORK_DATE VARCHAR(20)

SET @WORK_DATE = '20160210'
SET @SQL_TO_EXEC = 'SELECT * FROM OPENQUERY(DATA_BASE_1, ''SELECT EMPLOYEE.EMPLOYEE_NUMBER AS EMPLOYEE_ID FROM TABLE_1 EMPLOYEE LEFT JOIN TABLE_2 PERSON ON EMPLOYEE.EMPLOYEE_NUMBER = PERSON.EMPLOYEE_NUMBER AND PERSON.WORK_DATE = '''''+ @WORK_DATE + ''''''')'

EXEC (@SQL_TO_EXEC)

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 21 '16 at 21:38

Kamil Gosciminski

16,547
8
49
72

This works perfectly, I googled all over could not find much on Pass Through with variables so thank you. – MrNeedsaHand Feb 21 '16 at 23:12

Adding a parameter to a pass through query

1 Answers1