Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T-SQL using CASE in the WHERE clause

Posted on 2011-02-21
3
Medium Priority
?
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

598 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