Избегать разветвления в хранимых процедурах, возвращающих результаты поиска?

У меня есть приложение, которое должно возвращать результаты поиска из базы данных SQL Server 2008. Я хотел бы использовать одну хранимую процедуру для возврата результатов, но я нахожу, что по мере того, как я создаю хранимую процедуру, она заполнена множеством Else. Else If. Операторы с повторением запроса повторяются снова и снова с небольшими вариациями в зависимости от поиска пользователей критерии.

Есть ли лучший способ сделать это? Я пытаюсь избежать написания динамического SQL, потому что я хотел бы получить преимущества от плана выполнения, но я думаю, что должен быть лучший способ. Кто-нибудь имеет какие-либо предложения или, возможно, примеры того, как лучше всего создавать хранимую процедуру, которая должна иметь дело со многими параметрами поиска, многие из которых могут быть NULL? Спасибо.

Я всегда делал это, используя значения и условия по умолчанию; например

CREATE PROCEDURE [dbo].[searchForElement] ( @Town nvarchar(100) = '', @County nvarchar(100) = '', @postcode nvarchar(100) = '' ) AS BEGIN SET NOCOUNT ON; SELECT <fields> FROM table WHERE (@Town = '' OR Town LIKE '%'[email protected]+'%') AND (@County = '' OR County LIKE '%'[email protected]+'%') AND (@postcode = '' OR postcode LIKE '%'[email protected] +'%') END 

Редактировать:

Поскольку @gbn правильно рекомендует, приведенное выше приведет к сканированию индекса, что может быть проблемой для больших таблиц. Если это проблема, решение будет ниже, используя ISNULL, и тот факт, что добавление NULL к чему-либо приводит к NULL, это позволит искать индекс, потому что «%» понимается оптимизатором (тестируется на SQL2008). Это может быть менее читаемым, но оно лучше использует индексы.

 CREATE PROCEDURE [dbo].[searchForElement] ( @Town nvarchar(100) = NULL, @County nvarchar(100) = NULL, @postcode nvarchar(100) = NULL ) AS BEGIN SET NOCOUNT ON; SELECT <fields> FROM table WHERE Town LIKE ISNULL('%'[email protected]+'%', '%') AND County LIKE ISNULL('%'[email protected]+'%', '%') AND Postcode LIKE ISNULL('%'[email protected] +'%', '%') END 

На самом деле, нет.

С SQL Server 2005 и выше с перекомпиляцией уровня инструкций, в случае с предложениями OR есть меньше штрафа, а только сложности обслуживания.

Использование подхода Ричарда Харрисона делает его хуже, потому что OR не поддается продвижению, работает медленно, скорее всего, не будет использовать индексы.

Динамический SQL открывает SQL-инъекции, котировки и кеширование.

Это оставляет sp_executesql в соответствии с ответом CountZero, который по-прежнему требует создания строк.

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

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

http://ayyanar.blogspot.com/2007/11/performance-difference-between-exec-and.html

  • Проблема с параметрами, проходящими в динамическом t-sql
  • В SQL Server существует ли способ избежать использования курсора?
  • как решить параметр в sp_executesql
  • Как я могу создать цикл в инструкции UPDATE, которая работает до тех пор, пока не останется строка для обновления?
  • T-SQL: могу ли я использовать переменную в качестве ссылки на базу данных
  • TSQL - имя динамического столбца в динамическом SQL?
  • Yii: сортировка и форматирование динамических столбцов
  • В чем преимущество использования @ParmDefinition в sp_executesql
  • Могу ли я динамически вызывать хранимую процедуру из представления?
  • Сообщение об ошибке "не имеет параметров"
  • Альтернатива выполнению динамического sql
  • Interesting Posts

    Репликация SQL Azure Geo для целей без дублирования

    Рекомендации по составному индексу

    Общее выражение таблицы с вставкой и удалением

    Поиск SQL Server с использованием, например, при игнорировании пробелов

    Какой запрос будет выполняться быстрее, запрос, который использует объект таблицы или запрос, который использует временную таблицу в sql-сервере

    SQL Azure – как измерить текущую рабочую нагрузку, чтобы оценить, когда произойдет дросселирование

    SQL Server: выбор номера первичного ключа для новой добавленной строки

    Не одновременная фиксация в распределенной транзакции с участием EntityFramework / SQL Server и NServiceBus / MSMQ

    Является ли Brandoo WordPress безопасным для производственной среды?

    Обновление нескольких таблиц в одном операторе обновления с левым соединением

    SQL Server почему индекс не используется

    Как заставить SQL Server выполнять запрос в определенном порядке

    Как получить «1», если значение последнее в группе «OVER (order by something)» и «0», если нет?

    Оценка булевых выражений SQL Server

    Бит SQL-сервера восстановлен. Ошибка сбой логин

    Давайте будем гением компьютера.