conrad2010
asked on
T-SQL using CASE in the WHERE clause
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?
DECLARE
@searchField varchar(50)
,@searchTerm varchar(50)
SELECT
*
FROM
CompanyOrders
WHERE
(SELECT CASE
WHEN @searchField = 'REF' THEN OrderReferenceNumber = @searchTerm
WHEN @searchField = 'NAM' THEN OriginalCompanyName = @searchTerm
WHEN @searchField = 'DAT' THEN CompanyFilingDate = @searchTerm
END)
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?
DECLARE
@searchField varchar(50)
,@searchTerm varchar(50)
SELECT
*
FROM
CompanyOrders
WHERE
(SELECT CASE
WHEN @searchField = 'REF' THEN OrderReferenceNumber = @searchTerm
WHEN @searchField = 'NAM' THEN OriginalCompanyName = @searchTerm
WHEN @searchField = 'DAT' THEN CompanyFilingDate = @searchTerm
END)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strickdd
You can't use a CASE to determine a WHERE clause. You need to do an IF statement to separate the WHERE clauses. You can use a CASE for boolean logic in the WHERE, but it isn't recommeneded (e.g., MyCol = CASE WHEN @Filter=1 THEN @Cust ELSE Cust END).
Sorry, didn't refresh before posting.