T-SQL using CASE in the WHERE clause
Posted on 2011-02-21
this is a simplified code sample from a stored procedure that incorporates a CASE statement in the WHERE clause.
This is part of a Search function where we can search multiple fields with one statement, based upon the value of @searchField (and where @searchTerm has the value of what needs to be found).
This approach doesn't work, what would be the correct syntax?
WHEN @searchField = 'REF' THEN OrderReferenceNumber = @searchTerm
WHEN @searchField = 'NAM' THEN OriginalCompanyName = @searchTerm
WHEN @searchField = 'DAT' THEN CompanyFilingDate = @searchTerm