While loop: In SQL SERVER, while loop can be used in similar manner as any other programming language. A while loop will check the condition first and then execute the block of SQL Statements within it as long as the condition evaluates true.
Syntax:
WHILE condition
BEGIN
{...statements...}
END;Parameters:
1. Condition: The condition is tested in each pass through the loop. If condition evaluates to TRUE, the loop body is executed otherwise the loop is terminated.
2. Statements: The statements that needs to be executed in each pass through the loop.
Example:
Output:
Break statement: BREAK statement as the name signifies is used to break the flow of control. It can be used in SQL in similar manner as any other programming language.
Example: While loop with Break statement
Output:
Note : In the example, when variables value became five, BREAK Statement is executed and the control gets out from the Loop.
Do-While loop: SQL server does not have the feature of do-while loop but by doing little modifications in while loop, the same behaviour can be achieved.
Example 1:
Output:
Example 2:
Output:
CASE statement: In SQL Server, the CASE statement has the same functionality as IF-THEN-ELSE statement.
Syntax:
CASE Expression
WHEN Con_1 THEN Output1
WHEN Con_2 THEN Output2
WHEN Con_3 THEN Output3
WHEN Con_4 THEN Output4
...
WHEN Con_n THEN Outputn
ELSE output
END
Parameters:
1. Expression: The value to be compared to the list of conditions(Optional).
2. Con_1, Con_2, …Con_n: The conditions are required and are evaluated in the order they are listed. Once a condition is true, the CASE function will return the result and not evaluate the conditions any further.
3. Output1, Output2, …Outputn: The output to be printed once the condition evaluates true.
Example:
Output:
