过滤数据库表客户端 T-SQL select from case when then where 的任何列

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

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;

感谢您的时间

尝试这个查询：

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 and BirthDate有通配符语法，这将有助于部分匹配。
• 在一种特殊情况下，当您不提及任何列名称时，即@ColumnName = ''那么所有行将被返回，因为您不想过滤。您可以通过删除此行轻松更改此附加行为WHEN @ColumnName = '' THEN 1