Блог

Главная - Вопросы по программированию - Более аккуратный SQL-запрос для обработки необязательных фильтров предложения WHERE

Более аккуратный SQL-запрос для обработки необязательных фильтров предложения WHERE

#sql #sql-server-2005 #tsql #syntax

#sql #sql-server-2005 #tsql #синтаксис

Вопрос:

Пожалуйста, ознакомьтесь с инструкцией SQL ниже:

Есть ли лучший способ сделать это, исключив операторы case?

 select * from Customer 
where FirstName like ISNULL('ja','')   '%'  
AND [EmailId] LIKE ISNULL('jaisonshereen@gmail.com1','') 
      CASE when 'jaisonshereen@gmail.com1' =  '' then '%' else ''  end

Комментарии:

1. Есть ли причина, по которой вы объединяете ненулевые константы подобным образом? Вы имеете в виду IsNull (Клиент. Электронная почта, «)?

2. Также похоже, что вы пытаетесь сделать в SQL больше, чем необходимо. Я полагаю, вы выполняете это из некоторого кода (он выглядит сгенерированным)? Если это так, то операторы isnull / case было бы лучше выполнить там.

Ответ №1:

Основываясь на наблюдении @Thor84no, я параметризовал ваш запрос (предполагая, что это сгенерированный код) и принял следующие требования:

  • Оба @Firstname и @Email являются необязательными
  • Если @Firstname не является нулевым или пустым, всегда добавляйте к нему % и делайте подобное, в противном случае применяйте фиктивный фильтр (НАПРИМЕР, ‘%’)
  • Если @Email не является нулевым или пустым, выполните точный поиск (например, @EMAIL), в противном случае примените фиктивный фильтр (НАПРИМЕР, ‘%’)

Таким образом, он параметризуется следующим образом:

 DECLARE @FirstName NVARCHAR(100)
DECLARE @EmailId NVARCHAR(100)
SET @FirstName = 'ja'
SET @EmailId = 'jaisonshereen@gmail.com1'

select * from Customer 
where FirstName like ISNULL(@FirstName,'')   N'%'  
AND [EmailId] LIKE ISNULL(@EmailId, N'') 
      CASE when @EmailId =  N'' then N'%' else N''  end

Я полагаю, что есть случай, который вы, однако, не обрабатываете, а именно, если @Email имеет значение NULL — вам нужно изменить последнюю строку на 

   CASE when IsNull(@EmailId, '') =  N'' then N'%' else N''  end

План запроса вашего кода довольно хорош — он всегда будет ТАМ, ГДЕ FirstName КАК ‘..%’ И EMailId как ‘..’ (или EmailId как ‘%’) — вероятно, поэтому генератор кода делает это.

Хотя заманчиво сделать следующее для удобства чтения, «ИЛИ» нарушает план запроса и обычно приводит к сканированию таблицы / индекса

 select * from Customer 
WHERE 
(ISNULL(@FirstName, N'') = N'' OR FirstName LIKE @FirstName   N'%')
AND (ISNULL(@EmailId, N'') = N'' OR [EmailId] = @EmailId) -- Assuming ANSI Nulls are ON

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

Из интереса, динамический SQL, например, генерируемый ORM, такими как LINQ2SQL, EF и т. Д., Часто Имеет преимущество перед хранимым процессом в случаях, когда большое количество параметров являются необязательными. При использовании параметризованного SQL планы запросов все еще могут быть кэшированы, а запрос защищен от атак SQL-инъекций. Сравнить

 DECLARE @FirstName NVARCHAR(100)
DECLARE @EmailId NVARCHAR(100)
SET @FirstName = 'ja'
SET @EmailId = 'jaisonshereen@gmail.com1'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM Customer '

IF ISNULL(@FirstName,'') <> N'' OR ISNULL(@EmailId, N'') <> N''
    SET @SQL = @SQL   N'WHERE ' -- Need to handle the case where neither param provided

IF ISNULL(@FirstName, N'') <> N''
    SET @SQL = @SQL   N' FirstName LIKE @FirstName   ''%'''

IF ISNULL(@FirstName,'') <> N'' AND ISNULL(@EmailId, N'') <> N''
    SET @SQL = @SQL   N' AND'

IF ISNULL(@EmailId,'') <> N''
    SET @SQL = @SQL   N' EmailId = @EmailId' -- Exact match

exec sp_ExecuteSQL @SQL, N'@FirstName NVARCHAR(100), @EmailId NVARCHAR(100)', @FirstName=@FirstName, @EmailId=@EmailId