DATABASE & SQL/PLSQL

The CASE Statement

1. SQL server provide a programming construct called CASE statement
2. In this situation where several conditions need to be evaluated
3. The CASE statement evaluate a list of conditions and return one of the various possible results
4. Here we can use IF statement to do same task

Syntax:

CASE
    WHEN boolean_expression THEN expression
    [[WHEN boolean_expression THEN expression]..] 
    [ELSE expression]
END

where, boolean_expression is a Boolean expression that is evaluated when using the CASE statement.

Example:Give the position according to there Salary.

SELECT  emp_name, emp_sal, 'emp_position'= CASE
   WHEN  emp_sal=60000  THEN  'Team Leader'
   WHEN  emp_sal=45000  THEN  'Junior Developer'
   WHEN  emp_sal=80000  THEN  'Project Manager'
   WHEN  emp_sal=50000  THEN  'Developer'
ELSE  'Unknown'
END
FROM  employee

output

The WHILE Statement

1. While statement is used in a batch, a stored procedure, a trigger, or a cursor
2. Its allowed a set of T-SQL statements to execute repeatedly as long as the given condition hold true

Syntax:

WHILE boolean_expression

{sql_statement}
[BREAK]
{sql_statement}
[CONTINUE]

where, boolean_expression is an expression that evaluate to TRUE or FALSE.

sql_statement is any SQL statement.

BREAK cause the control to exit from the WHILE loop.

CONTINUE cause the WHILE loop to restart, skipping all the statements after the CONTINUE keyword.

SQL Server provides the BREAK and CONTINUE statements that help control the statements within the WHILE loop.

Example: Factorial of 5

DECLARE @num int
DECLARE @fact int
SELECT @num=5
SELECT @fact=1
WHILE @num>0
BEGIN
      SELECT @fact=@fact*@num
      SELECT @num=@num-1
END
PRINT @fact