How to Write an 'If Case' Statement as an Expression

Expression inside switch case statement

amount is a number, but the expressions in the case clauses only evaluate to booleans; the values will never match.

You could always do

switch (true) {
  case (amount >= 7500 && amount < 10000):
    // Code
    break;
  case (amount >= 10000 && amount < 15000):
    // Code
    break;
  // etc.
}

It works because the value being matched is now the boolean true, so the code under the first case clause with an expression that evaluates to true will be executed.

It’s kinda “tricky”, I guess, but I see nothing wrong with using it. A simple if–else statement would probably be more concise, and you’d not have to worry about accidental fall-through. But there it is anyway.

Case statement equivalent as an SSRS expressions

A nested if expression should do the trick. I haven't had a chance to test it, but it should look something like this:

=IIF((Fields!Location_Type.Value = "COMMER") AND (Fields!Contract.Value = "2)FRA Components"), "Commercial Units", IIF((Fields!Location_Type.Value <> "COMMER") AND (Fields!Contract.Value = "2)FRA Components"), "Non Commercial", Fields!Component_Description.Value))

IF else inside case when statement

We can nest CASE expressions, or use multiple tests if appropriate.

These 2 example give the same result.

select 
  x,y,
  case 
    when x = 1 then
      case when y = 1 then 11
      else 12 
      end
    when x = 2 then
      case when y = 1 then 21
      else 22
      end
  else 99 end myExression
from test;

x	y	myexression
1	2	12
1	1	11
2	1	21
2	2	22
null	null	99

SQL Server CASE Statement Evaluate Expression Once

You can use apply for this purpose:

 SELECT MyTable.ColumnA, 
        (CASE WHEN day_diff <= 0 THEN 'bad'
              WHEN BETWEEN 1 AND 15 THEN 'reasonable'
              ELSE 'good'
         END) as MyCalculatedColumn,
        MyTable.SomeOtherColumn
FROM MyTable CROSS APPLY
     (VALUES (DateDiff(day, MyTable.MyDate, getDate()))) v(day_diff)

APPLY is a very handy way to add calculated values into a statement. Because they are defined in the FROM clause, they can be used in SELECT, WHERE, and GROUP BY clauses where column aliases would not be recognized.

Can we write case statement without having else statement

A case expression can only manipulate the value of an expression, not remove rows from the result. If you want to omit the nulls from the result, you'll have to add a where clause:

SELECT CASE WHEN id = 1 THEN 'A'
            WHEN id = 2 THEN 'B'
       END 
FROM   test
WHERE  id IN (1, 2) -- HERE

Case Expression vs Case Statement

The CASE expression evaluates to a value, i.e. it is used to evaluate to one of a set of results, based on some condition.

Example:

SELECT CASE
    WHEN type = 1 THEN 'foo'
    WHEN type = 2 THEN 'bar'
    ELSE 'baz'
END AS name_for_numeric_type
FROM sometable`

The CASE statement executes one of a set of statements, based on some condition.

Example:

CASE
    WHEN action = 'update' THEN
        UPDATE sometable SET column = value WHERE condition;
    WHEN action = 'create' THEN
        INSERT INTO sometable (column) VALUES (value);
END CASE

You see how they are similar, but the statement does not evaluate to a value and can be used on its own, while the expression needs to be a part of an expression, e.g. a query or an assignment. You cannot use the statement in a query, since a query cannot contain statements, only expressions that need to evaluate to something (the query itself is a statement, in a way), e.g. SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE makes no sense.

SQL Case Expression Syntax?

The complete syntax depends on the database engine you're working with:

For SQL Server:

CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

or:

CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

expressions, etc:

case-expression    - something that produces a value
when-expression-x  - something that is compared against the case-expression
value-1            - the result of the CASE statement if:
                         the when-expression == case-expression
                      OR the boolean-when-expression == TRUE
boolean-when-exp.. - something that produces a TRUE/FALSE answer

Link: CASE (Transact-SQL)

Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.

Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL.

SSRS multiple IF expressions (SSRS Version Of a SQL CASE)

You don't need to use "then". IIF() syntax is simply

=IIF(Expression to evaluate, True result, False Result)

for example

=IIF(Fields!Score.Value > 75, "Pass", "Fail")

If you want to branch more than one test then you can nest IIF()s like this.

=IIF(Fields!Score.Value > 75, "Pass", IIF(Fields!Score.Value < 20, "BadFail",  "Fail"))

However, in your case a switch statement might be easier to read and debug rather than nesting lots of IIFs

=SWITCH(
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 4 Year 60,000 Miles Extended Warranty", 60000, 
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 3 Year 30,000 Miles", 30000,
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 4 Year 40,000 Miles", 40000,
   First(Fields!OtherModelsInfo.Value,"Agreement") = "V12 All V12 Models 2 Year 20,000 Miles", 20000,
   True, 0)

SWITCH uses pairs of expressions, the first the expression to test if it evaluates to true, the second is the result that should be returned. It stops at the first expression that evaluates to True.
The final True, 0 pair just acts like an else as "True" always evaluates to True. So in this case if none of the above match, you will get 0 returned.

With regards to the FIRST() function. You will only need this if the textbox containing the expression is within a group that aggregates rows of data with the same OtherModelsInfo field. If the textbox is within the details group then you don't need this.