I edit a query which contains a double openquery
. But in this query, I need to add a parameter. Because I never worked on SQL Server, I don't know how add this parameter to my query.
The query look like :
SELECT *
FROM openquery([1STCONNECTOR],'
SELECT *
FROM openquery([2NDCONNECTOR],''
SELECT *
FROM table
WHERE field = ''''PARAM'''''
)'
)
First, I tried :
SELECT *
FROM openquery([1STCONNECTOR],'
SELECT *
FROM openquery([2NDCONNECTOR],''
SELECT *
FROM table
WHERE field = ''''' + @PARAM + '''''
)'
)
But it doesn't work.
Then, I tried to separate the query like :
SET @query = 'SELECT *
FROM table
WHERE field = ' + @PARAM + ')'
SELECT *
FROM openquery([1STCONNECTOR],'
SELECT *
FROM openquery([2NDCONNECTOR],'''+ @VAR +''')')
But it also doesn't work.
The errors returned are about the syntax like :
Unexpected "+", expected ")"
Another attempt which looks like the previous :
SET @query1 = 'SELECT * FROM openquery([2NDCONNECTOR],' + @query2 + ')'
SET @query2 = 'SELECT * from BPCS61F.ITH WHERE field = ' + @PARAM + ')'
SELECT * FROM openquery([1STCONNECTOR], @query1)
Here, the error is on the third query, and say :
Syntax error close of "@query1". Expected STRING, or TEXT_LEX
@query1
and @query2
are both varchar(max)
Sorry if the syntax don't respect the conventions of SQL server language, I'm learning.
Thank you, Saul