Блог

У меня есть приложение, в котором пользователь может передать некоторые или все параметры, которые я запрашиваю, приложению. (В принципе, пользователь может прекратить вводить различные фрагменты данных, когда захочет.) Основываясь на вводимых ими данных, я хочу получить информацию из базы данных MS SQL 2008 только с теми параметрами, которые они мне предоставили.

Я представляю что-то подобное, но, похоже, я не могу заставить это работать, и понятия не имею, может ли это вообще.

 CREATE PROCEDURE [dbo].[SearchForSolution]
    @Input1 int = 0, 
    @Input2 int = 0,
    @Input3 int = 0,
AS
SELECT Name, SolutionValue
FROM MyTable
WHERE (IF @Input1 != 0) { BETWEEN LowValue AND HighValue }
      AND (IF @Input2 != 0) { BETWEEN LowValue AND HighValue }
      AND (IF @Input3 != 0) { < HighValue }         
RETURN 0
  

Очевидно, что это не рабочий код, но он дает общую идею. Я хочу иметь возможность использовать переменную как часть предложения WHERE, но только если в ней не используется магическое число (которое в данном случае равно 0).

Я не очень хорошо знаком с возможностями SQL здесь и не уверен, возможно ли то, что я хочу сделать. Все, что я пробовал до сих пор, потерпело неудачу. Если я здесь полностью иду по неверному пути, я был бы признателен за указатель в правильном направлении.

Вот подход…

  SELECT 
     Name, 
     SolutionValue
 FROM 
     MyTable
WHERE
     //Compact syntax simple for optional equals filter
     FIELD1  = ISNULL(@Input1,FIELD1) 
     //intention clearer and let's you do more than equals      
     AND ( @Input2 IS NULL OR FIELD2 > @Input2 ) 
     //can be extended for Between... 
     AND ((@Input3 IS NULL or  @Input4 IS NULL)   OR  FIELD3 BETWEEN  @Input3 AND @Input4 )
  

Один из способов:

 WHERE ( (@Input1 = 0) OR (@Input1 BETWEEN LowValue AND HighValue) )
  AND ( (@Input2 = 0) OR (@Input2 BETWEEN LowValue AND HighValue) ) 
  AND ( (@Input3 = 0) OR (@Input3 < HighValue) )
  

Вы также можете изменить эти @InputX = 0 условия на @InputX IS NULL , если хотите 0 , чтобы они были доступны для использования во вторых проверках.

Очень распространенным требованием в информационной системе является наличие одной или нескольких функций, с помощью которых пользователи могут осуществлять поиск в данных, свободно выбирая из множества возможных критериев. Это сложная задача, потому что вы должны не только выдавать желаемый результат, но и сохранять время отклика в допустимых пределах, по крайней мере, для обычных поисковых запросов. И, помимо всего прочего, код должен быть поддерживаемым, чтобы вы могли легко модифицировать его в соответствии с новыми потребностями.

Существует два способа решения этой проблемы: динамический SQL и статический SQL. До SQL 2005 было трудно найти решения для статического SQL, которые были бы просты в написании и обслуживании и при этом хорошо выполнялись, и была рекомендация использовать динамический SQL. В SQL 2008 все изменилось. Microsoft изменила ПАРАМЕТР подсказки (ПЕРЕКОМПИЛИРОВАТЬ), так что теперь он работает так, как вы ожидаете. Однако в первоначальной реализации была серьезная ошибка, и для использования этой функции требуется по крайней мере CU5 SQL 2008 SP1 или SQL 2008 SP2.

Эрланд Соммарског провел окончательный анализ различных подходов к этой проблеме и их соответствующих плюсов и минусов.

http://www.sommarskog.se/dyn-search-2008.html

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