CASE

Syntax

CASE
WHEN boolean expression THEN result expression [, ...]
[ELSE result expression]
END

CASE expression
WHEN expression THEN result expression [, ...]
[ELSE result_expression]
END

boolean expression syntax

expression {= | > | < | >= | <= | != | <>} {expression }

expression BETWEEN lower_expression AND upper_expression

expression IS [NOT] NULL

expression [NOT] LIKE 'sql_like_string'

boolean expression {AND | OR} boolean expression

results expression can be a column, function or constant.

Evaluates a list of conditions and returns one of multiple possible result expressions. CASE statement can be used in SELECT or SET (UPDATE). If none conditions match, case returns (null).

Examples

Select

select id, case when id in(0,1) then 0 when id between 2 and 10 then 100 when id > 10 and id < 20 then 1000 end id2 from system.systables

select case substring(name,0,3) when 'sys' then 'system table' else 'not system table' end   from system.systables

Update (from TPC-C test)

update CUSTOMER set C_BALANCE=C_BALANCE-@p4, C_YTD_PAYMENT=C_YTD_PAYMENT+@p4, C_PAYMENT_CNT=C_PAYMENT_CNT+1, C_DATA = case when c_credit = 'BC' then SUBSTRING(concat(c_id,c_d_id,c_w_id,@did,@wid,convert(@p4,char),getdate(),c_data),0,500) when c_credit <> 'BC' then c_data end where (C_W_ID=@p1)and(C_D_ID=@p2)and(C_ID=@p3)

Related