Using Parameters in OPENQUERY

Question

I have linked server connection named MMSHO. There is a parameterized function in Oracle server where I want to send store codes by OPENQUERY then filter in where condition. :

DECLARE
       @OPENQUERY   NVARCHAR (MAX),
       @STOREID     VARCHAR (2),
       @ARTICLEID   VARCHAR (10),
       @SQL         NVARCHAR (MAX)

SET @STOREID = 10;
SET @ARTICLEID = 245511;
SET @OPENQUERY = 'SELECT *
                  FROM OPENQUERY (MMSHO,
                      ''SELECT MS000'
           + @STOREID
           + '77TRP.MW070K01.MW070P01_REKE@MS000'
           + @STOREID
           + '77TRP(ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
                                    FROM MS000'
           + @STOREID
           + '77TRP.MW_MA_VAR_GEBI_SNAP@MS000'
           + @STOREID
           + '77TRP
                                    WHERE SUBSYS_ART_NR = '
           + @ARTICLEID
           + '''';

    SET @SQL =
             'SELECT STORE_NO,
           SUBSYS_ART_NO,
           ARTICLE_NO,
           ARTICLE_DESC,
           PUAR,
           SUPPLIER_NO,
           SORTEN_TEXT AS ARTICLE_VARIANT,
           GEBI_NR,
           BLOCK_CD,
           PACKAGE_CONTENT AS CONTENT,
           PACKAGE_TYPE AS CONTENT_TYPE,
           VAT AS ARTICLE_VAT,'
           + CHAR (13)
           + '('
           + @OPENQUERY
           + ') AS ARTICLE_NNBP,
           ARTICLE_NNSP,
           STOCK_QTY AS ARTICLE_STOCK,
           CASE WHEN DEPT_NO IS NULL THEN 0 ELSE DEPT_NO END DEPARTMENT
           FROM ARTICLE
           WHERE STORE_NO = 10 AND SUBSYS_ART_NO = 245511'

    PRINT (@SQL)

When I run the above query with EXEC (@SQL), SQL Server throws an error:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'CASE'.

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'DEPARTMENT'.

I have tried to remove the lines between OPENQUERY and FROM keyword but no success. What am I doing wrong? Could you please help me?

PRINTED QUERY

SELECT STORE_NO,
       SUBSYS_ART_NO,
       ARTICLE_NO,
       ARTICLE_DESC,
       PUAR,
       SUPPLIER_NO,
       SORTEN_TEXT AS ARTICLE_VARIANT,
       GEBI_NR,
       BLOCK_CD,
       PACKAGE_CONTENT AS CONTENT,
       PACKAGE_TYPE AS CONTENT_TYPE,
       VAT AS ARTICLE_VAT,
       (SELECT *
          FROM OPENQUERY (
                  MMSHO,
                  'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
                                FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
                                WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP,
       ARTICLE_NNSP,
       STOCK_QTY AS ARTICLE_STOCK,
       CASE WHEN DEPT_NO IS NULL THEN 0 ELSE DEPT_NO END DEPARTMENT
       FROM ARTICLE
       WHERE STORE_NO = 10 AND SUBSYS_ART_NO = 245511

Why Select is coming in single quote after executing the query — mohan111, Apr 05 '16 at 10:38
in 2nd query why 'SELECT MS0001077TRP.MW070K01 ' is showing in single quotes — mohan111, Apr 05 '16 at 10:40
There appears to be a missing closing bracket at the end of the `OPENQUERY` subquery, before `AS ARTICLE_NNBP`. — Ed Harper, Apr 05 '16 at 10:44
but after executing the print statement also that one is showing in single quotes then what is the purpose of adding variables . if you avoid that there will be no error in Case Condition because statement seems fine — mohan111, Apr 05 '16 at 10:45

score 0 · Accepted Answer · answered Apr 05 '16 at 11:41

The problem related with closing bracket, thanks @marcello-miorelli for helps.

It should be like that :

   (SELECT *
      FROM OPENQUERY (
              MMSHO,
              'SELECT MS0001077TRP.MW070K01.MW070P01_REKE@MS0001077TRP(
ART_NR, VAR, GEBI_NR) AS ARTICLE_NNBP
FROM MS0001077TRP.MW_MA_VAR_GEBI_SNAP@MS0001077TRP
WHERE SUBSYS_ART_NR = 245511') AS ARTICLE_NNBP),

Using Parameters in OPENQUERY

1 Answers1