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
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);
