Оператор SQL if в предложении where для поиска базы данных

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

Я хочу, чтобы выражение if было в моей статье where, но не могло заставить его работать. Предложение where должно фильтровать только ненулевые параметры.

Здесь sp

ALTER PROCEDURE spVillaGet 
-- Add the parameters for the stored procedure here
@accomodationFK int = null,
@regionFK int = null,
@arrivalDate datetime,
@numberOfNights int,
@sleeps int = null,
@priceFloor money = null,
@priceCeil money = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select tblVillas.*, tblWeeklyPrices.price from tblVillas
INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK
where 
    If @accomodationFK <> null then
        accomodationTypeFK = @accomodationFK 
     @regionFK <> null Then
        And regionFK = @regionFK 
    IF @sleeps <> null Then
        And sleeps = @sleeps 
    IF @priceFloor <> null Then
        And price >= @priceFloor And price <= @priceCeil


END

Любые идеи, как это сделать?

Ответы

Ответ 1

select tblVillas.*, tblWeeklyPrices.price 
from tblVillas
INNER JOIN tblWeeklyPrices on tblVillas.villaId = tblWeeklyPrices.villaFK
where (@accomodationFK IS null OR accomodationTypeFK = @accomodationFK)
  AND (@regionFK IS null or regionFK = @regionFK)
  AND (@sleeps IS null OR sleeps = @sleeps)
  AND (@priceFloor IS null OR (price BETWEEN @priceFloor And @priceCeil))

Ответ 2

Вы также можете использовать функцию IsNull или Coalesce

Where accomodationTypeFK = IsNull(@accomodationFK, accomodationTypeFK)
    And regionFK = Coalesce(@regionFK,regionFK)
    And sleeps = IsNull(@sleeps,sleeps ) 
    And price Between IsNull(@priceFloor, Price) And IsNull(priceCeil, Price)

Это делает то же самое, что и предложение Майкла выше...

IsNull() и Coalesce() работают более или менее одинаково, они возвращают первый не-нулевой аргумент в списке, за исключением того, что iSNull допускает только 2 аргумента, а Coalesce может принимать любое число...

http://blogs.msdn.com/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

Ответ 3

Мы использовали много COALESCE здесь в прошлом для динамических предложений WHERE", как вы говорите.

SELECT *
FROM  vehicles
WHERE ([vin]   LIKE COALESCE(@vin, [vin])     + '%' ESCAPE '\')
  AND ([year]  LIKE COALESCE(@year, [year])   + '%' ESCAPE '\')
  AND ([make]  LIKE COALESCE(@make, [make])   + '%' ESCAPE '\')
  AND ([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '\')

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

Чтобы компенсировать поля с нулевым значением, вам в конечном итоге придется делать беспорядочно выглядящий SQL так:

SELECT *
FROM  vehicles
WHERE (([vin]   LIKE COALESCE(@vin, [vin])     + '%' ESCAPE '\')
       OR (@vin IS NULL AND [vin] IS NULL))
  AND (([year]  LIKE COALESCE(@year, [year])   + '%' ESCAPE '\')
       OR (@year IS NULL AND [year] IS NULL))
  AND (([make]  LIKE COALESCE(@make, [make])   + '%' ESCAPE '\')
       OR (@make IS NULL AND [make] IS NULL))
  AND (([model] LIKE COALESCE(@model, [model]) + '%' ESCAPE '\')
       OR (@model IS NULL AND [model] IS NULL))

Ответ 4

Просто, чтобы вы поняли, ЕСЛИ это процедурный код в T-SQl. Он не может использоваться в инструкции insert/update/delete/select, он может использоваться только для определения того, из какого из двух операторов вы хотите запустить. Когда вам нужны разные возможности в инструкции, вы можете сделать это выше или использовать оператор CASE.

Ответ 5

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