The CASE statement and WHILE statement
Previous Home Next

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


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

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'
FROM  employee


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


WHILE boolean_expression


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
      SELECT @fact=@fact*@num
      SELECT @num=@num-1
PRINT @fact
output 120
Previous Home Next