• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

CASE Statement Syntax - ??? never saw befor.

Hi, Everyone:

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

What does it mean?

Thank you,

      SELECT TOP (@maxIndexSize) @requestID
            , svo.MlsNum
      FROM
            mls_unified_svo_tbl svo (nolock)
      WHERE
            svo.LoadOnInternet = 1
            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
DBA2000
Asked:
DBA2000
  • 2
3 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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 ...

Open in new window

0
 
DBA2000Author Commented:
I get it!  Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now