Syntax for SQL IN when using Excel database connection

Question

I'm using a number of Excel files to connect to an SQL Server database, fetching sales data for a particular product number which is specified in a cell in the Excel sheet.

This was set up by defining parameters in the SQL connection with

SELECT ProductCode, Sales
    WHERE ProductCode = ?

and specifying a cell in the worksheet that contains the product number. This works fine, however I now want to extract the sales for a number of products at once. I've created a comma-separated list automatically in Excel, but trying to use this as the parameter always results in this error message:

Syntax error message screenshot

I'm entering the SQL code as WHERE ProductCode IN ? as ideally I'd like to have the parameter cell able to change dynamically within Excel, but I've also tried WHERE ProductCode IN (1,2,3) or WHERE ProductCode IN (N'1', N'2', N'3') and various other different syntax. I've also replicated this syntax in the Excel-generated list of product numbers. (1,2 and 3 in this case being example product codes).

Some alternatives I could use would be having WHERE ProductCode=? OR ProductCode=? OR ProductCode=? etc and specify each as a separate parameter, but it's likely to be at least 200 product codes so this is far from ideal, it also means I'll have to manually edit the SQL code whenever a greater number of codes need to be fetched than however many cells I've set as parameters, and most colleagues don't have the SQL knowledge to do this in my absence either.

Probably I'm just using the incorrect syntax, but hopefully someone will be able to point out where I might be going wrong. The error message is the same no matter what format I select so I can't figure out what the problem might be.

This is the full query which does work:

SELECT
PRODUCT.ProductCode
,SALES.Quantity
,SALES.Date

FROM
  SALES
INNER JOIN PRODUCT
    ON SALES.ProductCode = PRODUCT.ProductCode
WHERE
  ((SALES.Date >=? AND SALES.Date<=?)
AND PRODUCT.ProductCode=?

Answer 1

Draft code for 2017+

-- output list
SELECT PRODUCT.ProductCode
      ,SALES.Quantity
      ,SALES.Date

-- data sources
FROM       SALES
INNER JOIN PRODUCT
        ON SALES.ProductCode = PRODUCT.ProductCode
-- including CSV codes list transferred as single parameter and parsed to rowset
INNER JOIN (SELECT value
            FROM STRING_SPLIT(?,',')) AS parameters(productCode)
        ON PRODUCT.ProductCode = parameters.productCode

-- filtering by date range transferred as a pair of parameters
WHERE (SALES.Date >=? AND SALES.Date<=?)

Simplified fiddle (filtering by SALES.Date removed)

it seems to be SQL Server 2014, compatibility level 120.

The solution applicable for SQL Server 2014:

-- output list
SELECT PRODUCT.ProductCode
      ,SALES.Quantity
      ,SALES.Date

-- data sources
FROM       SALES
INNER JOIN PRODUCT
        ON SALES.ProductCode = PRODUCT.ProductCode
-- including CSV codes list transferred as single parameter and parsed to rowset
INNER JOIN (SELECT b.n.value('@n', 'int')
            FROM (SELECT CAST('<item n = "' + REPLACE(?, ',', '"/><item n="') + '"/>' AS XML)) a(x) 
            CROSS APPLY a.x.nodes('item') b(n)) AS parameters(productCode)
        ON PRODUCT.ProductCode = parameters.productCode

-- filtering by date range transferred as a pair of parameters
WHERE (SALES.Date >=? AND SALES.Date<=?)

Simplified fiddle (filtering by SALES.Date removed)