I often need to join tables in my SQL Server DB with tables in a
linked Oracle server. I use OPENQUERY to do this. The problem is that
OPENQUERY don't support parameters, so I find it hard to add a WHERE
clause--for a specific date for example. I need a where clause or else
I'll pull hundreds of thousands of records.
My solution has been to dynamically construct the entire OPENQUERY
statement and assign it to a variable. Using this pattern:
DECLARE rSQL varchar(100)
DECLARE SQL varchar(100)
SELECT rSQL = 'SELECT ' + 'Getdate() as value'
SELECT SQL = 'SELECT ...