SQL linked server, open query using variable

Question

SELECT * INTO #C_INFO FROM OPENQUERY(ORACLE_CMIDW1,' SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between
''2012-03-01'' And ''2013-10-31'' AND Not A.ISSUE = 110 AND A.TYPE_ID = 1
and B.ENT_ID In (2,3) ')

Above sql works perfect but i dont want to hard code the dates. That's the reason trying to use variable. date format (yyyy-mm-dd, '2013-03-01'), anything other than this date format, Oracle doesn't execute the sql.

Is it possible to do this, If so please re write the whole sql using variable so that i can see how it can be done.

Thanks!

I dont any error, i want to replace those hard coded date(s) using variables. If i can do it, shoot me the query so that can i see how it can be done. — user1810575, Jul 01 '13 at 18:40
Very closely related; possibly duplicate of: http://stackoverflow.com/questions/3378496 — NateJ, Aug 26 '16 at 22:13

score 8 · Accepted Answer · answered Jul 01 '13 at 18:44

The below code should do the trick. You can't pass a variable to an OPENQUERY. However, you can dynamically generate the code that you will execute via OPENQUERY.

DECLARE @StartDate DATETIME = '2012-03-01';
DECLARE @EndDate DATETIME = '2013-10-31';

DECLARE @SqlCommand NVARCHAR(MAX) = N'SELECT * 
INTO #C_INFO 
FROM OPENQUERY(ORACLE_CMIDW1,
    ''SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID
    FROM XXX B
    INNER JOIN YYY A
        ON B.ANO = A.ANO
    WHERE A.REVIEW_COMP_DATE BETWEEN ''''' + CONVERT(CHAR(10), @StartDate, 120) + ''''' AND ''''' + CONVERT(CHAR(10), @EndDate, 120) + '''''
    AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 and B.ENT_ID In (2,3)'');';

PRINT @SqlCommand

EXEC sp_ExecuteSQL @SqlCommand;

On a separate note, I would recommend looking into replacing your linked server query with a SSIS package that loads the data. Although the above code works, this is not the most efficient way to transfer data from an Oracle server to SQL Server.

Supported by a KB article from MS themselves: https://support.microsoft.com/en-us/kb/314520 — NateJ, Aug 26 '16 at 22:12

score 0 · Answer 2 · answered Jul 01 '13 at 18:52

This should do it:

DECLARE @startDate DATETIME = '2013-06-01'
DECLARE @endDate DATETIME  = '2013-07-01'

INSERT INTO #C_INFO 
EXECUTE('SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between ? And ? 
AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 AND B.ENT_ID In (2,3) ', @startDate , @endDate) AT ORACLE_CMIDW1

SQL linked server, open query using variable

2 Answers2