SQL Server – Using Parameters in OPENQUERY

sql server

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 throw an error message which is :

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

Best Answer

I think:

this code:

       (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,

should be:

       (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),

there is a ")" missing at the end

Related Question