Игнорирование параметра NULL в T-SQL

Я хочу иметь возможность передать список параметров и игнорировать те, которые являются NULL. Так что запрос действительно делает вид, что фильтр не существует и игнорирует его.

Я делал это так:

(@thing IS NULL or [email protected])

Правильно ли это, и если да, то это будет плохо? Это, кажется, намного медленнее, чем создание SQL отдельно.

Какой оптимальный способ сделать это?

ИСПРАВЛЕНО! См. Ответ Марка Гравелла. Вкратце использование IS NULL много раз - большой успех.

Ответ 1

Как только вы получите больше, чем пару из них, тогда да: он начинает становиться довольно медленным. В таких случаях я имею тенденцию использовать сгенерированный TSQL - i.e.

DECLARE @sql nvarchar(4000)
SET @sql = /* core query */

IF @name IS NOT NULL
    SET @sql = @sql + ' AND foo.Name = @name'

IF @dob IS NOT NULL
    SET @sql = @sql + ' AND foo.DOB = @dob'

// etc

EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime',
        @name, @dob

и т.д.

Обратите внимание, что sp_ExecuteSQL кэширует запросы-планы, поэтому любые запросы с теми же аргументами могут потенциально повторно использовать план.

Недостатком является то, что, если вы не подписываете SPROC, вызывающей стороне нужны разрешения SELECT в таблице (а не только разрешения EXEC для SPROC).

Ответ 2

Я бы справился с этим так.

WHERE Thing = ISNULL(@Thing, Thing)

Если вы просто используете параметр в качестве фильтра в предложении where, это будет работать очень хорошо. Он будет игнорировать параметр, если он равен нулю.

Ответ 3

Обычно я использую

WHERE (id = @id OR @id IS NULL)
AND (num = @num OR @num IS NULL)

и др.

Ответ 4

Техника, используемая в прошлом для этого сценария, заключается в использовании функции COALESCE как части моего предложения WHERE. Books Online предоставит более подробную информацию о функции, но представляет собой фрагмент того, как вы можете использовать его в описанном вами сценарии:

create procedure usp_TEST_COALESCE
(
    @parm1 varchar(32) = null,
    @parm2 varchar(32) = null,
    @parm3 int = null
)
AS

SELECT * 
FROM [TableName]
WHERE Field1 = COALESCE(@parm1, Field1)
AND Field2 = COALESCE(@parm2, Field2)
AND Field3 = COALESCE(@parm3, Field3)

Функция COALESCE вернет первое ненулевое выражение из своих аргументов. В приведенном выше примере, если какой-либо из параметров имеет значение null, функция COALESCE будет использовать значение в базовом поле.

Одно из важных предостережений в использовании этого метода заключается в том, что базовые поля в таблице (которые составляют ваше предложение where) должны быть не-nullable.

Ответ 5

Я не уверен, что это "оптимальный" способ, но это именно то, что я делаю в своих хранимых процедурах для тех же целей. Я чувствую, что это быстрее, чем динамически созданный запрос, с точки зрения плана выполнения. Другой вариант - создать запрос для каждой комбинации этих "флагов", которые вы передаете, но это действительно не так масштабируемо.

Ответ 6

Посмотрите следующую ссылку в разделе "Пример исследования: поисковые заказы" . Это подробно исследует все варианты и должно дать вам отличный обзор затрат, связанных с каждым из этих вариантов. Предупреждение, будьте очень осторожны при использовании COALESCE, он не может вернуть то, что вы думаете.

Привет,

Тим

Ответ 7

Это метод, который я обычно использую. Я не вижу причин для его неэффективности, поскольку оператор должен замыкаться на истину, если @thing имеет значение NULL и поэтому не требует сканирования таблицы. У вас есть доказательства того, что это сравнение замедляет ваш запрос? Если нет, я бы не стал беспокоиться об этом.

Ответ 8

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

Обычно я просто проверяю его как

Поле IF NULL

Ответ 9

Спасибо, Это было полезно. Я решил использовать метод sp_ExecuteSQL из-за упомянутых потенциальных преимуществ производительности. У меня немного другое мнение, которое вы можете найти полезным.

DECLARE @sql nvarchar(4000) 
DECLARE @where nvarchar(1000) =''

SET @sql = 'SELECT * FROM MyTable'

IF @Param1 IS NOT NULL 
    SET @where = @where + ' AND Field1 = @Param1'

IF @Param2 IS NOT NULL 
    SET @where = @where + ' AND Field2 = @Param2' 

IF @Param3 IS NOT NULL 
    SET @where = @where + ' AND Field3 = @Param3' 

-- Add WHERE if where clause exists, 1=1 is included because @where begins with AND
IF @where <> ''
    SET @sql = @sql + ' WHERE 1=1' + @where

--Note that we could also create order parameters and append here
SET @sql = @sql + ' ORDER BY Field1'

Ответ 10

Если Thing (значение столбца) также имеет значение Nullable, используйте следующую команду: подход:

WHERE COALESCE(Thing,'')=COALESCE(@thing,Thing,'')