Условные соединения - динамический SQL

Вы можете INNER JOIN для таблицы, возвращенной из UDF, вместо использования ее в предложении IN

Ваш UDF может быть чем-то вроде

CREATE FUNCTION [dbo].[csl_to_table] (@list varchar(8000) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
    DECLARE     @index INT,
            @start_index INT,
            @id INT

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN

            SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
            INSERT @list_table ([id]) VALUES (@id)
            SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
    INSERT @list_table ([id]) VALUES (@id)
    RETURN
END

а затем INNER JOIN для идентификаторов в возвращенной таблице. Этот UDF предполагает, что вы передаете INT в своем списке, разделенном запятыми.

Обновлено:

Чтобы обработать нулевое или нулевое значение, передаваемое для @filter, наиболее простой способ, который я вижу, - это выполнить другой запрос в sproc на основе значения @filter. Я не уверен, как это влияет на кешированный план выполнения (обновится, если кто-то подтвердит) или если конечный результат будет быстрее, чем ваш исходный sproc, я думаю, что ответ здесь будет заключаться в тестировании.

Похоже, что переписывание кода рассматривается в другом ответе, но хорошим аргументом против динамического SQL в хранимой процедуре является то, что он нарушает цепочку владения.

То есть, когда вы вызываете хранимую процедуру в обычном режиме, она выполняется с разрешениями владельца хранимой процедуры, ЗА ИСКЛЮЧЕНИЕМ при выполнении динамического SQL с командой execute, для контекста динамического SQL она возвращается обратно к разрешениям вызывающей стороны, что может быть нежелательным в зависимости от вашей модели безопасности.

В конце концов, вам, вероятно, лучше пойти на компромисс и переписать его, чтобы решить проблемы администратора базы данных, избегая при этом динамического SQL.

Я не уверен, что понимаю ваше отвращение к динамическому SQL. Возможно, дело в том, что ваш UDF красиво абстрагировался от некоторой запутанности проблемы, и вы чувствуете, что динамический SQL вернет это обратно. Что ж, учтите, что большинство, если не все инструменты DAL или ORM будут в значительной степени полагаться на динамический SQL, и я думаю, что ваша проблема может быть переформулирована как «как я могу красиво абстрагироваться от беспорядка динамического SQL».

Что касается меня, динамический SQL дает мне именно тот запрос, который я хочу, и, следовательно, производительность и поведение, которые я ищу.

Я не вижу ничего плохого в вашем подходе. Честно говоря, мне кажется глупым переписывать его для использования динамического SQL для выполнения двух разных запросов в зависимости от того, имеет ли @Filter значение null.

Единственный потенциальный недостаток того, что у вас есть, - это то, что это может вызвать некоторые трудности при определении хорошего плана выполнения. Но если производительность и так достаточно хороша, нет причин менять ее.

Независимо от того, что вы делаете (и все ответы здесь имеют положительные моменты), обязательно сравните производительность и планы выполнения каждого варианта.

Иногда ручная оптимизация просто бессмысленна, если она влияет на ремонтопригодность вашего кода и действительно не влияет на то, как код выполняется.

Сначала я бы просто посмотрел на замену IN на простой LEFT JOIN с проверкой NULL (это не избавляет от вашего udf, но его нужно вызывать только один раз):

SELECT *
FROM table
LEFT JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value
WHERE @Filter IS NULL
    OR filter.Value IS NOT NULL

Похоже, вы пытаетесь написать один запрос для двух сценариев:
1. @filter = "x, y, z"
2. @filter ЕСТЬ NULL

Чтобы оптимизировать сценарий 2, я бы предпочел INNER JOIN в UDF, чем использовать предложение IN ...

SELECT * FROM table
INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
  ON table.FilterField = filter.Value

Чтобы оптимизировать сценарий 2, я бы НЕ пытался адаптировать существующий запрос, вместо этого я бы сознательно разделил эти случаи, либо оператор IF, либо UNION, и имитировал IF с предложением WHERE ...

TSQL IF

IF (@filter IS NULL)
  SELECT * FROM table
ELSE
  SELECT * FROM table
  INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value

UNION для моделирования IF

SELECT * FROM table
  INNER JOIN dbo.udfGetTableFromStringList(@Filter, ',') AS filter
    ON table.FilterField = filter.Value

UNION ALL

SELECT * FROM table WHERE @filter IS NULL

Преимущество таких конструкций состоит в том, что каждый случай прост, а определить, какой из них проще, проще простого. Однако объединение этих двух элементов в один запрос приводит к таким компромиссам, как LEFT JOINs, и, таким образом, приводит к значительной потере производительности для каждого из них.