DATABASES
FOR BUSINESS
CONTROL STRUCTURES
Lecturer, PH.D. Andreea bogoslov
[Link]@[Link]
What are Control-of-Flow Structures in SQL Server?
▸ Control-of-Flow structures (control
structures) are used to control the
sequence of SQL statement execution.
▸ These structures allow for conditional
execution, repetitive loops, and other
forms of logical flow management in
SQL Server.
2
Types of Control-of-Flow Structures in SQL Server
▸ IF...ELSE: Conditional structure for executing code based
on specific criteria.
▸ CASE: A conditional expression that allows you to perform
different actions based on conditions, similar to a switch
statement in other programming languages.
▸ BEGIN...END: Delimiters for grouping multiple statements as
a single block.
▸ WHILE: Loop structure for repeated execution as long as a
condition is true.
▸ BREAK and CONTINUE: Commands for breaking or skipping
an iteration in a loop.
▸ GOTO: Direct control transfer to a labeled part of the code.
▸ TRY...CATCH: Error-handling structure in SQL Server.
3
Minimal sYntax The IF...ELSE
IF condition
Structure
-- Code executed if the condition is true
ELSE
-- Code executed if the condition is false
EXAMPLE:
IF (SELECT Salary FROM Employees WHERE Name =
'Smith') > 5000 PRINT 'High salary’
ELSE
PRINT 'Low salary'
4
Minimal sYntax
SQL Server allows nested IF statements
within an IF...ELSE structure. This
means you can have one IF statement
inside another, which is useful for
checking multiple layers of conditions in
a structured way.
However, it’s essential to manage
nesting carefully, as deeply nested
conditions can make code harder to read
and maintain.
5
The CASE
Statement
Minimal sYntax
EXAMPLE: Classifying employees by
CASE salary level.
WHEN condition1 SELECT Name, Salary,
CASE
THEN result1
WHEN Salary > 5000
WHEN condition2
THEN 'High'
THEN result2
WHEN Salary BETWEEN 3000 AND 5000
... THEN 'Medium'
ELSE default_result ELSE 'Low'
END END AS SalaryLevel
FROM Employees
6
BEGIN...END
Minimal sYntax Used to group multiple
statements as a single
code block.
EXAMPLE:
IF (SELECT COUNT(*) FROM Products WHERE Stock > 0) > 10
BEGIN
PRINT 'Sufficient stock'
UPDATE Products SET Price = Price * 0.9
WHERE Stock > 10
END
7
Minimal sYntax The WHILE
The WHILE Structure
Structure
WHILE condition
-- Code that executes as long as the condition is true
EXAMPLE: Displaying the first 5 numbers.
DECLARE @i INT = 1
WHILE @i <= 5
BEGIN
PRINT @i
SET @i = @i + 1
END 8
Minimal sYntax
BREAK and CONTINUE EXAMPLE:
DECLARE @i INT = 1
▪ BREAK: Exits the loop and WHILE @i <= 10
moves on to the next BEGIN
statement. SET @i = @i + 1
IF @i = 5 CONTINUE
▪ CONTINUE: Skips the rest IF @i = 8
of the code in the loop for BREAK
the current iteration and PRINT @i
moves to the next one. END
9
Minimal sYntax EXAMPLE:
DECLARE @i INT = 1
GOTO Structure GOTO Start
Used to transfer control to a specified label. PRINT 'This will not print’
Start:
PRINT 'Control transferred to Start label'
EXAMPLE:
BEGIN TRY
DECLARE @value INT
TRY...CATCH Structure SET @value = 1 / 0
Used for error handling by catching errors in the END TRY
TRY block and handling them in the CATCH block. BEGIN
CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE()
END CATCH
10
THANK YOU!
ANY QUESTIONS?
Lecturer, PH.D. Andreea bogoslov
[Link]@[Link]