Solved

T-SQL using CASE in the WHERE clause

Posted on 2011-02-21
3
468 Views
Last Modified: 2012-05-11
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)
0
Comment
Question by:conrad2010
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34945826
DECLARE
      @searchField varchar(50)
      ,@searchTerm varchar(50)

SELECT
      *
FROM
      CompanyOrders
WHERE
      (@searchField = 'REF' AND OrderReferenceNumber = @searchTerm) OR
            (@searchField = 'NAM' AND OriginalCompanyName = @searchTerm) OR
            (@searchField = 'DAT' AND CompanyFilingDate = @searchTerm)

0
 
LVL 28

Expert Comment

by:strickdd
ID: 34945850
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).
0
 
LVL 28

Expert Comment

by:strickdd
ID: 34945857
Sorry, didn't refresh before posting.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question