You are here
Home > Uncategorized > Case Statement in SQL

Case Statement in SQL

CASE EXPRESSION

  • T-SQL supports an expression called CASE. Statement performs some kind of an action or controls flow of the code. CASE returns a value or an expression.
  • CASE expression has two forms:
  1. Simple form
  2. Searched form
  • SQL supports many functions that can be considered as abbreviates of the CASE expression. Those are the nonstandard ISNULL, IIF and CHOOSE, and the standard COALESE and NULLIF functions.

Simple form:

  • It compares an input expression to multiple possible scalar when expressions (in this case 1 and 0) and returns result expression associated with first match.
  • If there is no match and ELSE clause is specified, the else expression is returned.
  • If there is no ELSE clause then default ELSE NULL returned.
  • Syntax:
  •   Select columnlist,

    CASE input_expression

    When when_expression THEN ‘result_expression’

    ELSE else_expression

    END

    From tablename;

  • Database Table name: Product_demo

Id

name size date
1 Piya S

2017-02-02

2

Anil M 2017-04-04
3 Tina L

NULL

4

Shiv S

2017-08-08

 

  • Example:
  • select name, size,

    CASE size

    when ‘S’ then ‘small’

    when ‘M’ then ‘medium’

    when ‘L’ then ‘large’

    ELSE ‘n/a’

    END as productsizes

    from Product_demo;

 

 

  • Output:

name

size productsizes
Piya S

small

Anil

M medium
Tina L

large

Shiv

S

Small

 

Searched form:

  • Instead of comparing input expression to multiple possible expression, it uses predicates in WHEN clause and first predicate evaluates to true determines which when expression is returned.
  • If none is true then expression returns else expression.
  • Searched form is more flexible.
  • Syntax:
  • Select columnlist,

    CASE

    When boolean_expression THEN  ‘result_expression

                 ELSE else_expression

    END

    From tablename;

  • Database Table name: Product_demo

Id

name size date
1 Piya S

2017-02-02

2

Anil M 2017-04-04
3 Tina L

NULL

4

Shiv S

2017-08-08

 

 

  • Example:
  • select name, date,

    CASE

    when date IS NULL then ‘on sale’

    ELSE ‘sale over’

    END as status

    from Product_demo;

 

 

  • Output:

Name

date status
Piya 2017-02-02

sale over

Anil

2017-04-04 sale over
Tina NULL

on sale

Shiv

2017-08-08 sale over

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top