August 20, 2014

SQL - Case Statement

Case statement is used as conditional operator in Structure Query Language which compares the expression and returns the possible results. Case statement can also be used to evaluate a set of Boolean expression. Most often Case statement is useful in stored procedures, views and formula for a particular column while designing the table.

Syntax:

CASE expression
         WHEN expression1 THEN expression1
         WHEN expression2 THEN expression2
          .....
ELSE expressionN
END

--------------------------------------------------------------------------------------------------------------------------

For example:

DECLARE @Val INT = 1

SELECT
CASE @Val
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Not Known'
END

Result: First
--------------------------------------------------------------------------------------------------------------------------

DECLARE @Val INT  = 5

SELECT
CASE
WHEN @Val < 0 THEN 'Negative'
WHEN @Val < 3 THEN 'Less than 3'
WHEN @Val > 3 THEN 'Greater than 3'
ELSE 'Value not assigned'
END

Result: Greater than 3

--------------------------------------------------------------------------------------------------------------------------

Formula for a particular column while designing the table.

Below, you can see on the formula
(case [RoleID] when (1) then '100' when (2) then '75' when (3) then '50' else '0' end)


Result:



When Role is assigned to the Sales man then it will automatically set the value on the SalesTarget column based on the RoleID assigned.

No comments: