Хранимая процедура не возвращает все записи при передаче нескольких значений параметрам

Question

Я знаю, что эта тема обсуждалась здесь много раз. Но я потратил 2 дня и все еще не мог выяснить проблему с моим кодом. Любая помощь будет принята с благодарностью.

У меня есть хранимая процедура, которая ожидает 6 параметров, и я передаю несколько значений для них. Хранимая процедура возвращает правильные записи, когда выбрано одно значение.

Пожалуйста, найдите код хранимой процедуры, и я использую udf [ufnsplit], который используется для разделения переданных значений в таблицах

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

Я использую функцию

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

Когда я передаю несколько значений, возвращается 0 записей или первое значение, переданное в параметр. Я передаю такие значения, как

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

Я выполнил простой запрос к базе данных, просто чтобы проверить, есть ли данные для этих критериев. Я получаю данные обратно

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' 

Может кто-нибудь сказать, что я делаю не так? Я потратил на это много часов, но все равно не повезло.

Спасибо!

Простите за длинный пост.

Answer 1

Что вы должны сделать, это избавиться от новых строк в ваших параметрах, потому что:

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

это не то же самое, что

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

и вы говорите своей функции, что единственным разделителем является запятая (,) это не так, это запятая и новая строка для первого фрагмента кода, который по существу будет , + CHAR(13) + CHAR(10), это CHAR(13) + CHAR(10) на самом деле означает новую строку. Для того, чтобы прочитать немного больше на эту тему, я бы направил вас к этой маленькой статье. Объясняет проблему напрямую, и это работает в обоих направлениях (когда вы "ищете" что-то вроде вас или когда вы хотите добавить новые строки в (n)varchar).