Stored procedure not returning all records when passing multiple values to the parameters

Question

I know that this topic has been discussed many a times here. But I have spent 2 days and still couldn't figure out the issue with my code. Any help would be greatly appreciated.

I have a stored procedure that expects 6 parameters and I am passing multiple values for them. The stored procedure returns the correct records when single value is selected.

Please find the stored procedure code, and I am using an udf [ufnsplit] that is used to split passed values in tables

CREATE PROCEDURE [dbo].[Test1]
    @BUClass AS NVARCHAR(MAX),
    @Office AS NVARCHAR(MAX),
    @writer AS NVARCHAR(MAX),        
    @From AS DATETIME,
    @To AS DATETIME,
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_WARNINGS ON

   ;WITH UWS AS 
   (
       SELECT
           [Prefix],
           BusinessUnit,
           Office,
           Writer,
           FormName,
           Company
       FROM 
           A
       WHERE    
           BUClass IN (SELECT item FROM dbo.ufnSplit(@BUClass, ','))
           AND Office IN (SELECT item FROM [dbo].[ufnSplit](@Office, ','))
           AND Writer IN (SELECT item FROM [dbo].[ufnSplit](@writer, ','))
           AND Expiry_DT >= @From
           AND Expiry_DT <= @To
    ) 
    SELECT * 
    FROM UWS
END

The function I am using is

CREATE FUNCTION [dbo].[ufnSplit]
    (@RepParam NVARCHAR(MAX), @Delim CHAR(1)= ',')
RETURNS @Values TABLE (Item NVARCHAR(100))
AS
BEGIN
    DECLARE @chrind INT
    DECLARE @Piece NVARCHAR(100)

    SELECT @chrind = 1 

    WHILE @chrind > 0
    BEGIN
        SELECT @chrind = CHARINDEX(@Delim, @RepParam)

        IF @chrind  > 0
            SELECT @Piece = LEFT(@RepParam, @chrind - 1)
        ELSE
            SELECT @Piece = @RepParam

        INSERT INTO @Values(Item) VALUES(@Piece)

        SELECT @RepParam = RIGHT(@RepParam, LEN(@RepParam) - @chrind)

        IF LEN(@RepParam) = 0 
            BREAK
    END

    RETURN
END

When I pass multiple values, 0 records are returned, or the first value passed into the parameter. I am passing values like

DECLARE @return_value int

EXEC    @return_value = [dbo].[Test1]
                @Office = N'ATLANTA, 
                            BERKELEY HEIGHTS, 
                            BOSTON, 
                            CHICAGO, 
                            CLEVELAND',
                @BusinessClass = N'Commercial Management Solutions,
                                   HEALTHCARE, 
                                   PROGRAMS - PROFESSIONAL, 
                                   CANADA',
                @writer = N'Grant Duggar, 
                            Alyssa Gulich, 
                            Amanda Kelly,   
                            Daniel Chavenson, 
                            Deborah Wilieko, 
                            Derek Barbe'
                @From = N'1/1/2018',
                @To = N'4/1/2018',

I have ran a simple query against the database, just to check that there is data for these criteria. I get data back

SELECT DISTINCT * 
FROM A
WHERE BUclass IN (SELECT DISTINCT BUClass FROM A)
  AND Office IN (SELECT DISTINCT Producing_Office FROM A)
  AND UWName IN (SELECT DISTINCT UWName FROM A)
  AND Expiry_DT = '1/1/2018' 
  AND Expiry_DT <= '4/1/2018' 

Can anyone tell what I am doing wrong? I have spent lots of hours on it but still no luck .

Thanks !

Sorry for the long post.

Answer 1

What you must do is get rid of the new lines in your parameters because:

@BusinessClass = N'Commercial Management Solutions,
                                   HEALTHCARE, 
                                   PROGRAMS - PROFESSIONAL, 
                                   CANADA'

is not the same as

@BusinessClass = N'Commercial Management Solutions,HEALTHCARE,PROGRAMS - PROFESSIONAL,CANADA'

and you're telling your function that the only delimiter is comma (,) which it isn't, it's comma and a new line for the first snippet of code which would essentially be , + CHAR(13) + CHAR(10). This CHAR(13) + CHAR(10) actually means new line. In order to read up a bit more on the subject I'd direct you towards this little article. Explains the issue directly and this works both ways (when you're "searching" for something like you are or when you want to add new lines to a (n)varchar).