SQL Function inside of where clause to see if field is in concatenated list (from parameter)

I have these two lines

,SUM (CASE WHEN (DATEDIFF(DAY,Shopify_Ordered,Confirmed) >= 1)THEN 1 ELSE 0 END) OVER () Orders1DayNotShipped
,dbo.GROUP_CONCAT( (CASE WHEN (DATEDIFF(DAY,Shopify_Ordered,Confirmed) >= 1)THEN Customer_Purchase_Order_Number END)) OVER () Orders1DayNotShippedString

The first line counts the number of orders that are One Day Not shipped. The second line uses a Group_Concat function (found here: https://archive.codeplex.com/?p=groupconcat) to return a list of the order numbers of those late orders.

Here is a sample result: 91149220,91155318,91155319, etc

Now what I want to do is take that result/field and then pass it to another report through SSRS. I can pass this parameter to the other report easily, I just need some help making it actually work. I want the second report to take those order numbers and show me all those orders.

So for my second report, I set up the parameter and this is what I tried to do for my where clause

WHERE 1=1 
    ...
    AND (@LocalOrderList IS NULL OR (Customer_Purchase_Order_Number in dbo.[Z_N_CSVToList](@LocalOrderList)))

Z_N_CSVToList is a simpe function that breaks apart a string into a list. This is it:

ALTER FUNCTION [dbo].[Z_N_CSVToList] (@CSV varchar(MAX)) 
    RETURNS @Result TABLE (Value varchar(300))
AS   
BEGIN
    DECLARE @List TABLE
    (
        Value varchar(300)
    )

    DECLARE
        @Value varchar(300),
        @Pos int

    SET @CSV = LTRIM(RTRIM(@CSV))+ ','
    SET @Pos = CHARINDEX(',', @CSV, 1)

    IF REPLACE(@CSV, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))

            IF @Value <> ''
                INSERT INTO @List (Value) VALUES (@Value) 

            SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)
            SET @Pos = CHARINDEX(',', @CSV, 1)
        END
    END     

    INSERT @Result
    SELECT
        Value
    FROM
        @List

    RETURN
END

How do I make this work? Running it as it is right now gives me the error “Incorrect syntax near ‘dbo’.”

Do I have to add quotes around the order numbers in the group concat line?

EDIT: I figured it out. If I change my where clause to this, it works.

AND (@LocalOrderList IS NULL OR (Customer_Purchase_Order_Number in (Select * from dbo.[Z_N_CSVToList](@LocalOrderList))))

But now my report only returns the first order from the list, not all of them. Any idea why?

Answer

should be something like this using exists

AND (@LocalOrderList IS NULL OR 
(exists (select 1 from dbo.[Z_N_CSVToList](@LocalOrderList) v
where Customer_Purchase_Order_Number = v.value
)))

Advertisement

Answer