如何从Server中的多个where语句中获得正确的值

Question

我有一个搜索引擎，用户将指定多个条件，基于这些条件，我将返回一个datatable。

我所面临的问题是，这些条件没有得到尊重，而且我得到了错误的结果。

我试着单独测试每一种情况，它是有效的，但是当我把所有的条件组合在一起时，我得到了意想不到的结果。

USE [Tenant Management]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RentSearchEngine]
    @sqm INT,
    @category INT,
    @shortRentPrice MONEY,
    @longRentPrice MONEY,
    @fromDate DATETIME,
    @toDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        c.[Name], cate.[Channel Category], tp.type, st.Status, c.Surface,
        g.GOVERNATOR + ' ' + d.District + ' ' + cit.City + ' ' as [Address],
        c.[Short term Price per night] AS [Short term monthly amount],
        c.[Long term price per month] AS [Long term monthly amount],
        c.[Selling Price]
    FROM
        [dbo].[Channel] c
    INNER JOIN 
        [dbo].[Governator] g ON c.[Governator ID] = g.ID
    INNER JOIN 
        [dbo].[District] d ON c.[District ID] = d.ID
    INNER JOIN 
        [dbo].[City] cit ON c.[City ID] = cit.id
    INNER JOIN
        [dbo].[Channel_Category] cate ON c.[Channel Category ID] = cate.ID
    INNER JOIN
        [dbo].[Channel_Type] tp ON c.[Channel Type] = tp.id
    INNER JOIN
        [dbo].[Channel_Status] st ON c.[Channel Status] = st.ID
    LEFT JOIN
        [dbo].[Reservations] r ON c.[ID] = r.[Channel ID]
    WHERE 
        c.[Channel Status] = '5'
        AND c.[Channel Type] = '1'
        AND c.[Channel Category ID] = @category OR @category IS NULL
        AND c.Surface BETWEEN @sqm * 0.85 AND @sqm * 1.15 OR @sqm IS NULL
        AND c.[Long term price per month] BETWEEN @longRentPrice * 0.85 
                                              AND @longRentPrice * 1.15 OR @longRentPrice IS NULL
        AND c.[Short term Price per night] BETWEEN @shortRentPrice * 0.85 
                                               AND @shortRentPrice * 1.15 OR @shortRentPrice IS NULL
        AND (r.[Actual Date in] > @fromDate AND r.[Actual Date out] > @toDate)
        AND (r.[Actual Date in] < @fromDate AND r.[Actual Date out] < @toDate)
END

目前的结果是：

  fdfd  Residential apatment    For Rent    Available   500  Mont Liban  Baabda  Ain El Remmaneh    1287182.00  28712.00    128712.00

执行存储过程时，如下所示：

 DECLARE    @return_value int
 EXEC   @return_value = [dbo].[RentSearchEngine]
    @sqm = 40000,
    @category = 1,
    @shortRentPrice = 5,
    @longRentPrice = 4,
    @fromDate = NULL,
    @toDate = NULL
SELECT  'Return Value' = @return_value

Answer 1

我认为关键的问题是运算符的偏好和& OR。记住这一点，并且有比OR更高的偏好。在这些复杂的条件下，确保使用括号的顺序是一种很好的做法。我会写下我理解你想要达到的目标，但确保你按你所要求的顺序使用括号：

where c.[Channel Status] = '5'
                and c.[Channel Type] = '1'
                and (c.[Channel Category ID] =@category or @category IS NULL)
                and (c.Surface between @sqm*0.85 and @sqm*1.15 or @sqm IS NULL)
                and (c.[Long term price per month] between @longRentPrice*0.85 and @longRentPrice*1.15 or @longRentPrice IS NULL)
                and (c.[Short term Price per night] between @shortRentPrice*0.85 and @shortRentPrice*1.15 or @shortRentPrice IS NULL)
                and (r.[Actual Date in] > @fromDate and r.[Actual Date out] > @toDate)
                and (r.[Actual Date in] < @fromDate and r.[Actual Date out] < @toDate)