用于搜索数据库的where子句中的SQL if语句 [英] SQL if statement in where clause for searching database

问题描述

我正在创建一个存储过程来返回搜索结果，其中一些参数是可选的。

I'm creating a stored procedure to return search results where some of the parameters are optional.

我想在我的中使用if语句 子句但无法使其正常工作。 where 子句应仅过滤非null参数。

I want an "if statement" in my where clause but can't get it working. The where clause should filter by only the non-null parameters.

这是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

任何想法如何做？

推荐答案

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))

这篇关于用于搜索数据库的where子句中的SQL if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！