1

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

Saul
  • 11
  • 4

0 Answers0