Multiple fields check in case statement in SQL Server 2008

Question

Case Statement syntax in MySQL:-

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

Case Statement syntax in SQL Server 2008:-

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 

Follow SQL SERVER CASE STATEMENT SYNTAX

I have explained the difference so that users can understand what I am tryng to say here

I have the following code in MySql:-

SELECT sum(case when Year=2014 or purchased=0 then 0 else TC.TOTAL_SP_COST-TC.TOTAL_CP_COST end) as EARNINGs

from TABLE TC

If you see properly the case statement has 2 fields (Year and purchased)

When i tried the same code in MS SQL SERVER 2008,i got a red line under "or" when i tried to add 2 field in case statement in SQL SERVER.

SELECT sum(case (Year or purchased) when 2014 then 0 when 0 then 0 else TC.TOTAL_SP_COST-TC.TOTAL_CP_COST end) as EARNINGs

So the simple question is "Can we add multiple fields in case statement in SQL SERVER ?" IF YES How can we do it ?

Answer 1

Did you try anything at all? The MySQL syntax you've posted and the MSSQL CASE are completely identical.

Edit for Revision 2:

The issue with your queries here is primary that you are mixing up the two valid forms for CASE syntax. There are two, and you are using the first in MySQL and mixing the two in your MSSQL query:

CASE
    WHEN <expression>
     AND <expression>
      THEN <result>
END

or

CASE <input>
    WHEN <expression>
     AND <expression>
      THEN <result>
END

Edit 3: Never Trust a MySQL

As MySQL is known to allow lots of completely whacky things, I went to see if the mis-ported query would even run on MySQL. Surprisingly ( but not shockingly ), it does. However, it is worth noting that the original MySQL query:

SELECT SUM( CASE 
           WHEN ( Year = 2014 OR purchased = 0 )
             THEN 0
           ELSE TC.TOTAL_SP_COST - TC.TOTAL_CP_COST 
       END ) AS EARNINGs
FROM `TABLE` TC;

Has a meaning which is far different than the MySQL-compatible second query, presented here with the SUM omitted and a second column to illustrate how the expression is evaluated:

SELECT CASE ( Year OR purchased ) 
           WHEN 2014 
             THEN 0 -- This line is unreachable;  [BOOLEAN]: { , 0, 1 }
           WHEN 0 
             THEN 0 -- This line only possible when Year == 0;  Bad Year data;
           ELSE TC.TOTAL_SP_COST - TC.TOTAL_CP_COST
       END AS EARNINGs,
       ( Year OR purchased ) AS Res
FROM `TABLE` TC;

Answer 2

The code you have in the question looks fine - whether you got a red squiggly line under the or or not. Did you even try to execute it, or did you assume that because there was a red line there, there's no possible way it would work? IntelliSense is anything but - often it is way behind or it complains about things elsewhere in your code that have nothing to do with the current statement. For example, do you think there's really a problem with the second statement here?

If I highlight that statement, the SELECT will still be underlined as if it is incorrect. That doesn't mean it will fail when I execute it.

Here is an example showing that your code works just fine, in spite of the red squiggly you're seeing - for probably a completely unrelated reason.

CREATE TABLE dbo.[TABLE]
(
  [Year] INT,
  purchased BIT,
  TOTAL_SP_COST INT,
  TOTAL_CP_COST INT
);

SELECT SUM(CASE WHEN [Year] = 2014 OR purchased = 0 THEN 0 
  ELSE TOTAL_SP_COST - TC.TOTAL_CP_COST END) AS EARNINGs
FROM dbo.[TABLE] AS TC;

Of course I made a couple of other adjustments, e.g. when you must use reserved words (even future keywords) as column names (you really should fix that), you should escape them properly, and you should always use the schema prefix and get in the habit of using semi-colons as statement terminators.

Meaningless SQL Fiddle example, since I have no idea of your sample data or desired results.

Finally, an important semantic thing: CASE is an expression, not a statement. The important thing you're missing from the syntax diagram is that the input and output of a searched CASE expression must itself be a single expression, and there are no boolean expressions in SQL Server. So you can't say:

CASE (col1 OR col2) WHEN something THEN ...