CASE Expression
The SQL Server (Transact-SQL) CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement. CASE expression is mostly used in SQL stored procedure or as a formula for a particular column, which optimizes the SQL statements.
You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.
SYNTAX:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN value_n THEN result_n
ELSE your_msg
END
EXAMPLE:
SELECT StudentId,
CASE RollNo
WHEN 1 THEN 'RAVI'
WHEN 2 THEN 'BHUSHAN'
....
WHEN 100 THEN 'THAKUR'
ELSE 'BATCH_2012-15'
END as StudentName
FROM tbStudent
Note:
1. If no value/condition is found to be TRUE, then the CASE statement will return the value in the ELSE clause.
2. If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
3. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
4. You can not use the CASE statement to control program flow, instead, use loops and conditional statements.
The SQL Server (Transact-SQL) CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement. CASE expression is mostly used in SQL stored procedure or as a formula for a particular column, which optimizes the SQL statements.
You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.
SYNTAX:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN value_n THEN result_n
ELSE your_msg
END
EXAMPLE:
SELECT StudentId,
CASE RollNo
WHEN 1 THEN 'RAVI'
WHEN 2 THEN 'BHUSHAN'
....
WHEN 100 THEN 'THAKUR'
ELSE 'BATCH_2012-15'
END as StudentName
FROM tbStudent
Note:
1. If no value/condition is found to be TRUE, then the CASE statement will return the value in the ELSE clause.
2. If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
3. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
4. You can not use the CASE statement to control program flow, instead, use loops and conditional statements.