SQL: OPENROWSET, can't build for the request string?

Question

I want to construct the query used with the OPENROWSET method.

Example:

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

But when I do that I get the error: Incorrect Syntax near '+'

How can I build the query? Thank

Answer 1

Although the query in OPENROWSET is specified as a string and by that means looks very much like a dynamic query, the syntax does not allow it to be constructed likewise, out of parts.

I'm afraid, you'll have to build a dynamic query, which will call OPENROWSET, something like this:

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