How to use Case and While statement in Batches: SQL

Database developer can use the CASE as well as While statement in situation where several conditions need to be evaluated.

Using CASE statement

The CASE statement evaluates a list of conditions and returns one of the possible results. You can use the IF statement to do the same task. However, you can use a CASE statement when there are more than two conditions that check a common variable for different values. The syntax of the CASE statement is:

CASE
WHEN Boolean_expression THEN expression
[ [WHEN Boolean_expression THEN expression] […..] ]
[ELSE expression]
END
Where,

Boolean_expression specifies a bollean expression that is evaluated when using the CASE construct.
Expression is the resultant expression that is executed when the Boolean expression evaluates to TRUE. This can be a constant, a column name, a function, a query, or any combination of arithmetic, bit-wise, and string operators.

In a simple CASE construct, a variable or an expression is compared with the Boolean expression in each WHEN clause. If any of these expressions evaluate to TRUE, then the expression specified with the THEN clause is executed. If the expression does not evaluate to TRUE, the expression with the ELSE statement is executed.

Consider the following example where a case construct is included in the SELECT statement to display the marital status as ‘Married’ or Single’:

SELECT EmployeeID, ‘Marital status’ =
CASE MaritalStatus
WHEN ‘M’ THEN ‘Married’
WHEN ‘S’ THEN ‘Single’
ELSE ‘Not specified’
END
FROM HumanResources.Employee
GO

Using the While Statement

You can use the WHILE statement in a batch to allow a set of T-SQL statement to execute repeatedly as long as the given condition holds true. The syntax of the WHILE statement is:

WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK]
{sql_statement | statement_block}
[CONTINUE]
Where,

Boolean_expression is an expression that evaluates to TRUE or FALSE.
Sql_statement is any SQL statement.
Statement_block is a group of SQL statements.
BREAK causes the control to exit from the WHILE loop.
CONTINUE causes the WHILE loop to restart, skipping all the statements after the CONTINUE keyword.

The SQL Server provides the BREAK and CONTINUE statements to control the statement within the WHILE loop. The BREAK statement causes an exit from the WHILE loop. Any statements that appear after the END keyword, which marks the end of the loop, are executed after the BREAK statement is executed. The CONTINUE statement causes the WHILE loop to restart, skipping any statements after this statement inside the loop.

Consider the following example where the HR department of AdventureWorks, Inc. has decided to review the salary of all the employees. As per the current HR policy, the average hourly salary rate of all the employees should be approximately $20. You need to increase the hourly salary of all the employees until the average hourly salary reaches near $20. In addition, you need to ensure that the maximum hourly salary should not exceed $127.

WHILE (SELECT AVG(Rate) +1 FROM HumanResources.EmployeePayHistory) <20
BEGIN
UPDATE HumanResources.EmployeePayHIstory
SET Rate = Rate +1
FROM HumanResources.EmployeePayHistory
IF (SELECT max (Rate) +1 FROM
HumanResources.EmployeePayHistory)>127
BREAK
ELSE
CONTINUE
END