SQL: OPENROWSET, Impossible to build for query string?

Question

I want to build a query used with the OPENROWSET method.

Example:

SELECT *
FROM
OPENROWSET
('SQLOLEDB', 'srv'; 'login'; 'mdp';
'SELECT *
 FROM Case
 WHERE ID = ' + @caseID) 

But when I do this, I get the error: Invalid syntax next to '+'

How can I build a query? thank

Answer 1

Although the query in OPENROWSETis specified as a string and, therefore, is very similar to a dynamic query, the syntax does not allow you to create it also from parts.

I'm afraid you will need to create a dynamic query that will call OPENROWSET, something like this:

SET @sql = '
  SELECT *
  FROM
  OPENROWSET
  (''SQLOLEDB'', ''srv''; ''login''; ''mdp'';
   ''SELECT *
     FROM Case
     WHERE ID = ' + @caseID + ''')';
EXEC(@sql);