Solved

# CASE Statement Syntax - ??? never saw befor.

Posted on 2011-10-10
Medium Priority
346 Views
Hi, Everyone:

I have never come accross this syntax in a CASE statement before; ... END = 1 ...

What does it mean?

Thank you,

SELECT TOP (@maxIndexSize) @requestID
, svo.MlsNum
FROM
mls_unified_svo_tbl svo (nolock)
WHERE
AND svo.std IS NOT NULL
AND svo.snd IS NOT NULL
AND (svo.Status IN (5, 30, 45)
OR
(svo.Status IN (10, 20)
AND DATEDIFF(day, svo.StatusDate, GETDATE()) < 365
)
)

AND
CASE WHEN @request.exist('/searchRequest/mlsNumber') = 0
THEN 1
ELSE
CASE WHEN svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)') THEN 1
ELSE 0
END
END = 1

AND
CASE WHEN @request.exist('/searchRequest/zipCode') = 0
THEN 1
ELSE
CASE WHEN svo.zip = @request.value('(/searchRequest/zipCode)[1]', 'varchar(500)') THEN 1
ELSE 0
END
END = 1

AND
CASE WHEN @request.exist('/searchRequest/city') = 0
THEN 1
ELSE
CASE WHEN svo.city = @request.value('(/searchRequest/city)[1]', 'varchar(50)') THEN 1
ELSE 0
END
END = 1
0
Question by:DBA2000
• 2

LVL 60

Assisted Solution

Kevin Cross earned 800 total points
ID: 36945650
It is because the CASE is in the WHERE clause. It needs to be a BOOLEAN expression; therefore, what it is doing is evaluating the conditions to either 0 or 1 and then comparing if you have met one of the 1 conditions. :)
0

LVL 71

Accepted Solution

Qlemo earned 1200 total points
ID: 36945651
The CASE END builds an expression (in this case resulting in either 0 or 1), and that expression is compared against a constant, =1. Since MSSQL does not know of boolean data type, you need to use a trick like this one to code special conditions.
0

LVL 71

Assisted Solution

Qlemo earned 1200 total points
ID: 36945665
It could also be coded with true conditions, like
``````AND  (
@request.exist('/searchRequest/mlsNumber') = 0 or svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)'
) AND ...
``````
0

Author Closing Comment

ID: 36945930
I get it!  Thank you very much.
0

## Featured Post

Question has a verified solution.

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

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.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month13 days, 8 hours left to enroll