Access pass-through query passing comma separated list as a parameter to SQL stored procedure

Question

The SQL server is 2008. I have an Access 2016 front-end for reporting purposes. One report requires that one or more Product Classes from a list be chosen to report on. I have the VBA that creates the pass-through query with the appropriate single line:

exec dbo.uspINVDAYS 'A3,A4,A6,AA,AB'

I have this SQL code that should take the list as hard-coded here:

DECLARE @parProductClasses NVARCHAR(200) = 'A3,A4,A6,AA,AB';
DECLARE @ProductClasses NVARCHAR(200),@delimiter NVARCHAR(1) = ',';
SET @ProductClasses = @parProductClasses;

DECLARE @DAYS INT,@numDAYS int;
SET @DAYS = 395;
SET @numDAYS = @DAYS;

SELECT UPINVENTORY.StockCode, UPINVENTORY.[Description], UPINVENTORY.Supplier, UPINVENTORY.ProductClass
    , UPINVENTORY.WarehouseToUse
    , CAST(UPINVENTORY.Ebq AS INT)Ebq
    , cast(UPINVENTORY.QtyOnHand AS INT)QtyOnHand
    , cast(UPINVENTORY.PrevYearQtySold AS INT)PrevYearQtySold
    , cast(UPINVENTORY.YtdQtyIssued AS INT)YtdQtyIssued
    ,@numDAYS as numDAYS
    ,CAST(ROUND((PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS,0) AS INT)TOTAL
    ,CASE WHEN (PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS
        = 0 THEN 0
        ELSE CAST(ROUND(QTYONHAND/((PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS),0)AS INT)
    END FINAL
    ,CASE WHEN (PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS
        = 0 THEN 0
        ELSE CAST(ROUND(QTYONHAND/((PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS),0)AS INT)
    END FINAL1
FROM 
TablesCoE.dbo.vwRPUpInventory UPINVENTORY
WHERE UPINVENTORY.ProductClass  IN (Select val From TablesCoE.dbo.split(@ProductClasses,','));

When I run this I get:

Msg 468, Level 16, State 9, Line 9
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.

I cannot determine where

COLLATE SQL_Latin1_General_CP1_CI_AS

should go. Where am I equating or comparing? The SQL IN clause cannot handle the comma-separated list since it is not a strict SQL table.

Here's the code used to create the dbo.split() function:

CREATE FUNCTION dbo.split(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT  r.value('.','varchar(MAX)') as item
FROM  @xml.nodes('/t') as records(r)
RETURN
END

Thanks to Sandeep Mittal and I am sure others have very similar split functions. Run separately this function does operate as expected and provides a table of the comma-separated list objects.

DECLARE @parProductClasses NVARCHAR(200) = 'A3,A4,A6,AA,AB';
DECLARE @ProductClasses NVARCHAR(200),@delimiter NVARCHAR(1) = ',';
SET @ProductClasses = @parProductClasses;
Select val From TablesCoE.dbo.split(@ProductClasses,',')

Returns

val
A3
A4
A6
AA
AB

I think what @krishKM is suggesting is that the issue may not be with your subquery that calls your split function. You could also try joining to your subquery rather then filtering on it in your where clause. — Isaac, Mar 10 '20 at 23:36
If I were to put the actual @ProductClasses list into the IN clause the query works: WHERE UPINVENTORY.ProductClass IN ('A3','A4','A6','AA','AB') works. I'll have to try without the WHERE clause when I get to work tomorrow but I am pretty sure I will just get every possible record. — Tim, Mar 10 '20 at 23:53

score 0 · Answer 1 · answered Mar 11 '20 at 08:46

0

try this.

WHERE concat(',',@ProductClasses,',') like concat('%',UPINVENTORY.ProductClass,'%')

it's a silly way of checking if your productClass is within the @productClasses list.

answered Mar 11 '20 at 08:46

Krish

5,917
2
14
35

The concat function is not implemented in SQL Server 2008! – Tim Mar 11 '20 at 12:10

Tim · Accepted Answer · 2020-03-11T13:20:41.003

After attempting to use a prefabricated table-valued variable versus on the fly in the WHERE clause, neither worked, I then started to try different placements of the COLLATE statement. I was complacent in applying COLLATE to the right-side with the collation listed on the left in the SQL error message. I tried the collation listed on the right of the SQL error message to the left side of the WHERE clause and the SQL code works to spec now. Here it is:

DECLARE @parProductClasses NVARCHAR(200) = 'A3,A4,A6,AA,AB';
DECLARE @ProductClasses NVARCHAR(200),@delimiter NVARCHAR(1) = ',';
SET @ProductClasses = @parProductClasses;

DECLARE @DAYS INT,@numDAYS int;
SET @DAYS = 395;
SET @numDAYS = @DAYS;

SELECT UPINVENTORY.StockCode, UPINVENTORY.[Description], UPINVENTORY.Supplier, UPINVENTORY.ProductClass
    , UPINVENTORY.WarehouseToUse
    , CAST(UPINVENTORY.Ebq AS INT)Ebq
    , cast(UPINVENTORY.QtyOnHand AS INT)QtyOnHand
    , cast(UPINVENTORY.PrevYearQtySold AS INT)PrevYearQtySold
    , cast(UPINVENTORY.YtdQtyIssued AS INT)YtdQtyIssued
    ,@numDAYS as numDAYS
    ,CAST(ROUND((PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS,0) AS INT)TOTAL
    ,CASE WHEN (PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS
        = 0 THEN 0
        ELSE CAST(ROUND(QTYONHAND/((PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS),0)AS INT)
    END FINAL
    ,CASE WHEN (PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS
        = 0 THEN 0
        ELSE CAST(ROUND(QTYONHAND/((PREVYEARQTYSOLD + YTDQTYISSUED)/@DAYS),0)AS INT)
    END FINAL1
FROM 
TablesCoE.dbo.vwRPUpInventory UPINVENTORY
WHERE UPINVENTORY.ProductClass COLLATE Latin1_General_BIN IN (SELECT val FROM TablesCoE.dbo.split(@ProductClasses,','));

Thanks for your suggestions @Krish and @Isaac. Tim

Access pass-through query passing comma separated list as a parameter to SQL stored procedure

2 Answers2