filtering any columns of a database table client T

问题:

I would like to be able filter through the columns of a datagrid but don't know how to fix the select statement this is as far as I could get

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;

Thank you for your time

回答1:

Try this query:

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

Query Explanation:

• When you pass any column name and filter which matches to any records and per column name, it will return those records.
• When column name matches and no record matches as per column name it fallback to last ELSE part so it won't return any records as expected.
• All the filters apart from ClientID and BirthDate has wild card syntax, which will help for partial match.
• In one special case when you don't mention any column name i.e. @ColumnName = '' then all rows will be returned as you didn't want to filter. You can easily change this additional behavior by removing this line WHEN @ColumnName = '' THEN 1

回答2:

I think or in the where is cleaner here

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

回答3:

You should try to refer to the actual column in your where clause, as @ColumnName contains name, rather than referring to the column value. By "WHERE @ColumnName = @Filter", we compare the name of the column. By "WHERE ClientID = @Filter", we compare the value of the column. Maybe, you should do it this way:

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 + '%';