SQL条件数据筛选器

Question

我有一个SQL存储过程，根据两个控制参数@FilterOption、@DateFilterOption的值，希望应用不同的筛选器。第一个字段确定字段是否等于、开头或包含并且正在正常工作。第二种方法用于确定日期是否等于、大于或小于以及是否工作正常。问题是他们不一起工作。

另外两个参数@FilterValue和@DateFilterValue是用于筛选的实际值。如果我使用all和all作为日期选项而不是OR的选项，那么它不能正确工作，那么在HAVING子句中是否有更好的方法来编码日期选项。

谢谢CM

create Procedure [dbo].[SelectPagedData]
(
    @FilterValue NVARCHAR(50),
    @CurrentPage INT,
    @PageSize INT,
    @TotalRecords INT OUTPUT,
    @OrderField INT,
    @FilterOption INT,
    @DateFilterOption INT,
    @DateFilterValue DATE
)

As

--FilterOption
--0=none
--1=CustomerStartsWith,
--2=CustomerContains,
--3=CreatedByStartsWith,
--4=CreatedByContains

--@DateFilterOption
--0=none
--1=CreatedByEquals,
--2=CreatedByGreaterThan,
--3=CreatedByLessThan,
--4=ModifiedOnEquals,
--5=ModifiedOnGreaterThan,
--6=ModifiedOnLessThan

-- Turn off count return.
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

IF @DateFilterOption = 0 
BEGIN
    SET @DateFilterValue = '1900-01-01' 
END

IF @FilterOption = 0 
BEGIN
    SET @FilterValue = 1 
END

IF @FilterOption = 1 OR @FilterOption = 3
BEGIN
    SET @FilterValue = @FilterValue + '%' 
END

IF @FilterOption = 2 OR @FilterOption = 4
BEGIN
    SET @FilterValue = '%' + @FilterValue + '%' 
END

PRINT @FilterValue

Create Table #TempTable
(
    NewQuoteID INT IDENTITY PRIMARY KEY,
    QuoteID INT,
    GUID UNIQUEIDENTIFIER,
    EquipmentID INT, 
    QuoteName NVARCHAR(50),
    AccountNumber NVARCHAR(50),
    ADName NCHAR(10),
    CreatedOn DATETIME,
    LastModifiedOn DATETIME,
    CustomerName NVARCHAR(100),
    EquipmentName NVARCHAR(200),
    Total DECIMAL(18,2)
)

--Fill the temp table
Insert INTO #TempTable 
(
    QuoteID,
    GUID,
    EquipmentID,
    QuoteName,
    AccountNumber,
    ADName,
    CreatedOn,
    LastModifiedOn,
    CustomerName,
    EquipmentName,
    Total
)
SELECT Q.QuoteID, Q.GUID, Q.EquipmentID, Q.QuoteName, Q.AccountNumber, Q.ADName, Q.CreatedOn, 
  Q.LastModifiedOn, C.CustomerName, E.EquipmentName, SUM(QD.Amount) Total
FROM 
    Quotes Q
JOIN
    Customers C
ON Q.AccountNumber = C.AccountNumber
JOIN
    Equipment E
ON E.EquipmentID = Q.EquipmentID
JOIN
    QuoteDetails QD
ON QD.GUID = Q.GUID
GROUP BY q.QuoteID, Q.GUID, Q.EquipmentID, Q.QuoteName, Q.AccountNumber, Q.ADName,
 Q.LastModifiedOn, Q.CreatedOn,C.CustomerName, E.EquipmentName
HAVING
    (CASE @FilterOption 
    WHEN 0 THEN '1'
    WHEN 1 THEN C.CustomerName 
    WHEN 2 THEN C.CustomerName 
    WHEN 3 THEN Q.ADName 
    WHEN 4 THEN Q.ADName 
    END) LIKE @FilterValue 
 AND
(@DateFilterOption = 0 AND CONVERT(date, Q.CreatedOn,101) > @DateFilterValue)  
OR
(@DateFilterOption = 1 AND CONVERT(date, Q.CreatedOn,101) = @DateFilterValue)  
OR
 (@DateFilterOption = 2 AND CONVERT(date, Q.CreatedOn,101) > @DateFilterValue)  
OR
 (@DateFilterOption = 3 AND CONVERT(date, Q.CreatedOn,101) < @DateFilterValue)  
OR
(@DateFilterOption = 4 AND CONVERT(date, Q.LastModifiedOn,101) = @DateFilterValue)  
OR
(@DateFilterOption = 5 AND CONVERT(date, Q.LastModifiedOn,101) > @DateFilterValue)  
OR
(@DateFilterOption = 6 AND CONVERT(date, Q.LastModifiedOn,101) < @DateFilterValue)  
ORDER BY
    CASE WHEN @OrderField = 0 THEN [QuoteName] END,
    CASE WHEN @OrderField = 1 THEN [QuoteName] END DESC,
    CASE WHEN @OrderField = 2 THEN CreatedOn END,
    CASE WHEN @OrderField = 3 THEN CreatedOn END DESC,
    CASE WHEN @OrderField = 4 THEN LastModifiedOn END,
    CASE WHEN @OrderField = 5 THEN LastModifiedOn END DESC,
    CASE WHEN @OrderField = 6 THEN CustomerName END,
    CASE WHEN @OrderField = 7 THEN CustomerName END DESC,
    CASE WHEN @OrderField = 8 THEN ADName END,
    CASE WHEN @OrderField = 9 THEN ADName END DESC

--Select one page of data based on the record numbers above
Select 
    QuoteID,
    GUID,
    EquipmentID,
    QuoteName,
    AccountNumber,
    ADName,
    CreatedOn,
    LastModifiedOn,
    CustomerName,
    EquipmentName,
    Total
From 
    #TempTable
Where 
    NewQuoteID > @FirstRec 
And 
    NewQuoteID < @LastRec
ORDER BY 
    CASE WHEN @OrderField = 0 THEN [QuoteName] END,
    CASE WHEN @OrderField = 1 THEN [QuoteName] END DESC,
    CASE WHEN @OrderField = 2 THEN CreatedOn END,
    CASE WHEN @OrderField = 3 THEN CreatedOn END DESC,
    CASE WHEN @OrderField = 4 THEN LastModifiedOn END,
    CASE WHEN @OrderField = 5 THEN LastModifiedOn END DESC,
    CASE WHEN @OrderField = 6 THEN CustomerName END,
    CASE WHEN @OrderField = 7 THEN CustomerName END DESC,
    CASE WHEN @OrderField = 8 THEN ADName END,
    CASE WHEN @OrderField = 9 THEN ADName END DESC

SELECT 
    @TotalRecords = COUNT(*)
FROM 
   #TempTable

Answer 1

我想问题就在你的括号里。尝试将having子句更改为：

HAVING (CASE @FilterOption 
            WHEN 0 THEN '1'
            WHEN 1 THEN C.CustomerName 
            WHEN 2 THEN C.CustomerName 
            WHEN 3 THEN Q.ADName 
            WHEN 4 THEN Q.ADName 
        END) LIKE @FilterValue AND
       ((@DateFilterOption = 0 AND CONVERT(date, Q.CreatedOn,101) > @DateFilterValue) OR
        (@DateFilterOption = 1 AND CONVERT(date, Q.CreatedOn,101) = @DateFilterValue) OR
        (@DateFilterOption = 2 AND CONVERT(date, Q.CreatedOn,101) > @DateFilterValue) OR
        (@DateFilterOption = 3 AND CONVERT(date, Q.CreatedOn,101) < @DateFilterValue) OR
        (@DateFilterOption = 4 AND CONVERT(date, Q.LastModifiedOn,101) = @DateFilterValue) OR
        (@DateFilterOption = 5 AND CONVERT(date, Q.LastModifiedOn,101) > @DateFilterValue) OR
        (@DateFilterOption = 6 AND CONVERT(date, Q.LastModifiedOn,101) < @DateFilterValue)
       ) 

另外两项评论。首先，在where子句中进行过滤通常更有效，因为它减少了聚合所需的数据量。其次，您不需要转换作为日期存储的日期值。而且，将日期存储为字符串是个坏主意。所以，不同的日期应该已经是日期了。如果它们是datetime，并且您只想将它们转换为date，那么将第三个参数移除到convert()。用于将字符串转换为日期的。(实际上，在这种情况下，我总是使用cast(col as date)，但cast()或convert()都可以。)