Избегать разветвления в хранимых процедурах, возвращающих результаты поиска?
У меня есть приложение, которое должно возвращать результаты поиска из базы данных SQL Server 2008. Я хотел бы использовать одну хранимую процедуру для возврата результатов, но я нахожу, что по мере того, как я создаю хранимую процедуру, она заполнена множеством Else. Else If. Операторы с повторением запроса повторяются снова и снова с небольшими вариациями в зависимости от поиска пользователей критерии.
Есть ли лучший способ сделать это? Я пытаюсь избежать написания динамического SQL, потому что я хотел бы получить преимущества от плана выполнения, но я думаю, что должен быть лучший способ. Кто-нибудь имеет какие-либо предложения или, возможно, примеры того, как лучше всего создавать хранимую процедуру, которая должна иметь дело со многими параметрами поиска, многие из которых могут быть NULL? Спасибо.
- Как использовать «EXEC @sql»?
- Неверный синтаксис Near ')' внутри хранимой процедуры
- SQL Server 2008 Хранимый Proc не возвращает результат динамического запроса
- Что происходит в этом коде T-SQL? (Объединение результатов оператора SELECT)
- И условие в Case Statement IN SQL Server
- Получить количество строк в результате хранимой процедуры независимо от числа столбцов / типов столбцов
- SP или Функция для UnPivoted данных с использованием имен столбцов
- Динамический поиск запросов SQL
- Varchar (Max) не работает в Exec
- Синтаксическая ошибка динамического запроса
- Каков эквивалент SQL Server переменных привязки Oracle в динамическом SQL?
- Создайте логин SQL с помощью sp_executesql proc
- Как присоединиться к динамическому оператору sql в переменной с нормальным выражением
Я всегда делал это, используя значения и условия по умолчанию; например
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