筛选数据库表客户端 T-SQL 的任何列，从大小写中选择何时何地

Question

我希望能够过滤数据网格的列，但不知道如何修复选择语句，这是我所能得到

的

SELECT 
    ClientID, FirstName, LastName, BirthDate, StreetName, 
    City, State, ZipCode, CellPhone 
FROM 
    dbo.Client 
CASE WHEN @ColumnName = 'ClientID' THEN WHERE @ColumnName = @Filter END
CASE WHEN @ColumnName = 'FirstName' THEN WHERE @ColumnName LIKE @Filter END 
CASE WHEN @ColumnName = 'LastName' THEN WHERE @ColumnName LIKE @Filter END 
CASE WHEN @ColumnName = 'BirthDate' THEN WHERE @ColumnName = @Filter END 
CASE WHEN @ColumnName = 'StreetName' THEN WHERE @ColumnName LIKE @Filter END 
CASE WHEN @ColumnName = 'City' THEN WHERE @ColumnName LIKE @Filter END 
CASE WHEN @ColumnName = 'State' THEN WHERE @ColumnName LIKE @Filter END 
CASE WHEN @ColumnName = 'ZipCode' THEN WHERE @ColumnName LIKE @Filter END  
CASE WHEN @ColumnName = 'CellPhone' THEN WHERE @ColumnName LIKE @Filter END;

谢谢你的时间

Answer 1

尝试以下查询：

SELECT ClientID, FirstName, LastName, BirthDate, StreetName, City, State, 
ZipCode, CellPhone 
FROM dbo.Client 
WHERE 
CASE WHEN @ColumnName = 'ClientID' AND ClientID = @Filter THEN 1
WHEN @ColumnName = 'FirstName' AND FirstName LIKE '%' + @Filter + '%' THEN 1
WHEN @ColumnName = 'LastName' AND LastName LIKE '%' + @Filter + '%' THEN 1
WHEN @ColumnName = 'BirthDate' AND CASE WHEN ISDATE(@Filter) = 1 THEN CONVERT(DATETIME, @Filter, 101) ELSE NULL END = BirthDate THEN 1
WHEN @ColumnName = 'StreetName' AND StreetName LIKE '%' + @Filter + '%' THEN 1
WHEN @ColumnName = 'City' AND City LIKE '%' + @Filter + '%' THEN 1
WHEN @ColumnName = 'State' AND State LIKE '%' + @Filter + '%' THEN 1
WHEN @ColumnName = 'ZipCode' AND ZipCode LIKE '%' + @Filter + '%' THEN 1 
WHEN @ColumnName = 'CellPhone' AND CellPhone LIKE '%' + @Filter + '%' THEN 1
WHEN @ColumnName = '' THEN 1
ELSE 0
END = 1

查询说明：

• 当您传递与任何记录和每个列名称匹配的任何列名和过滤器时，它将返回这些记录。
• 当列名匹配且没有记录与列名匹配时，它会回退到最后一ELSE部分，因此它不会按预期返回任何记录。
• 除 ClientID 和 BirthDate 之外的所有筛选器都具有通配符语法，这将有助于部分匹配。
• 在一种特殊情况下，当您不提及任何列名时，即 @ColumnName = ''，则将返回所有行，因为您不想过滤。您可以通过删除此行来轻松更改此附加行为WHEN @ColumnName = '' THEN 1

Answer 2

我认为where中的or在这里更干净

SELECT ClientID, FirstName, LastName, BirthDate, StreetName, City, State, 
       ZipCode, CellPhone 
FROM dbo.Client  
where (@ColumnName = 'ClientID'  and ClientID     = @Filter) 
   or (@ColumnName = 'FirstName' and FirstName LIKE @Filter)
   or (@ColumnName = 'LastName'  and LastName  LIKE @Filter) 
...;

Answer 3

您应该尝试引用 where 子句中的实际列，因为@ColumnName包含名称，而不是引用列值。通过"WHERE @ColumnName = @Filter"，我们比较列的名称。通过"WHERE ClientID = @Filter"，我们比较列的值。也许，你应该这样做：

SELECT ClientID, FirstName, LastName, BirthDate, StreetName, City, State, 
ZipCode, CellPhone 
FROM dbo.Client 
CASE 
  WHEN @ColumnName = 'ClientID' THEN ClientID
  WHEN @ColumnName = 'FirstName' THEN FirstName
  WHEN @ColumnName = 'LastName' THEN LastName 
  WHEN @ColumnName = 'BirthDate' THEN BirthDate
  WHEN @ColumnName = 'StreetName' THEN StreetName
  WHEN @ColumnName = 'City' THEN City
  WHEN @ColumnName = 'State' THEN State
  WHEN @ColumnName = 'ZipCode' THEN ZipCode
  WHEN @ColumnName = 'CellPhone' THEN CellPhone
END
LIKE '%' + @Filter + '%';