SQL Loops, Functions, and Conditions
SQL (Structured Query Language) is primarily a declarative language, meaning you
specify what you want to retrieve or manipulate, rather than how to do it. However,
many relational database management systems (RDBMS) extend SQL with procedural
capabilities, allowing for more complex logic, including loops, functions, and
conditional statements. These extensions are often found in procedural SQL dialects
like T-SQL (for SQL Server), PL/pgSQL (for PostgreSQL), and PL/SQL (for Oracle).
Loops in SQL
While standard SQL does not have a direct FOR loop construct like many programming
languages, procedural extensions provide ways to iterate. The most common looping
construct is the WHILE loop.
WHILE Loop
The WHILE loop repeatedly executes a block of statements as long as a specified
condition is true.
Syntax (T-SQL Example):
DECLARE @counter INT = 1;
WHILE @counter <= 5
BEGIN
PRINT 'Current counter value: ' + CAST(@counter AS VARCHAR);
SET @counter = @counter + 1;
END;
Explanation:
DECLARE @counter INT = 1; : Declares an integer variable counter and
initializes it to 1.
WHILE @counter <= 5 : The loop continues as long as counter is less than or
equal to 5.
BEGIN ... END; : Defines the block of statements to be executed within the
loop.
PRINT ... : Outputs the current value of counter .
SET @counter = @counter + 1; : Increments the counter in each iteration.
BREAK and CONTINUE Statements
BREAK : Exits the WHILE loop immediately.
CONTINUE : Skips the rest of the current iteration and proceeds to the next
iteration of the loop.
Example (T-SQL):
DECLARE @i INT = 0;
WHILE @i < 10
BEGIN
SET @i = @i + 1;
IF @i = 3
CONTINUE; -- Skip printing for i = 3
IF @i = 7
BREAK; -- Exit loop when i = 7
PRINT 'Value of i: ' + CAST(@i AS VARCHAR);
END;
PRINT 'Loop finished.';
Functions in SQL
SQL functions are used to perform calculations, manipulate data, and format output.
They can be categorized into built-in functions and user-defined functions.
Built-in Functions
SQL provides a rich set of built-in functions for various purposes:
Aggregate Functions: Perform calculations on a set of rows and return a single
value (e.g., COUNT() , SUM() , AVG() , MAX() , MIN() ).
SELECT COUNT(EmployeeID), AVG(Salary) FROM Employees;
Scalar Functions: Operate on a single value and return a single value (e.g.,
UCASE() , LCASE() , MID() , LEN() , ROUND() , NOW() ).
SELECT UCASE(FirstName) AS UpperName, LEN(LastName) AS LastNameLength
FROM Employees;
Date Functions: Work with date and time values (e.g., GETDATE() , DATEADD() ,
DATEDIFF() ).
SELECT GETDATE() AS CurrentDateTime, DATEADD(day, 7, GETDATE()) AS
NextWeek;
User-Defined Functions (UDFs)
Users can create their own functions to encapsulate complex logic or calculations that
are frequently used. UDFs can return a scalar value or a table.
Syntax (T-SQL Scalar Function Example):
CREATE FUNCTION CalculateTax (@amount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @tax DECIMAL(10, 2);
SET @tax = @amount * 0.05; -- 5% tax
RETURN @tax;
END;
-- Usage
SELECT [Link](100.00) AS TaxAmount;
Conditions in SQL
Conditional statements allow different actions to be performed based on whether a
condition is true or false. The most common conditional constructs are IF...ELSE
and CASE expressions.
IF...ELSE Statement
The IF...ELSE statement executes a block of code if a specified condition is true, and
an alternative block if the condition is false. This is typically used in procedural blocks
(e.g., stored procedures, functions).
Syntax (T-SQL Example):
DECLARE @score INT = 85;
IF @score >= 60
BEGIN
PRINT 'Passed';
END
ELSE
BEGIN
PRINT 'Failed';
END;
CASE Expression
The CASE expression is used to handle different conditions and return a value based
on those conditions. It can be used within SELECT , WHERE , and ORDER BY clauses.
Syntax (Simple CASE ):
SELECT ProductName,
ProductPrice,
CASE ProductPrice
WHEN 100 THEN 'Expensive'
WHEN 50 THEN 'Moderate'
ELSE 'Cheap'
END AS PriceCategory
FROM Products;
Syntax (Searched CASE ):
SELECT EmployeeName,
Salary,
CASE
WHEN Salary > 70000 THEN 'High Earner'
WHEN Salary >= 40000 THEN 'Mid-Range Earner'
ELSE 'Low Earner'
END AS SalaryBracket
FROM Employees;
References
[5] SQLShack. (2019, October 25). SQL WHILE loop with simple examples.
[Link]
[6] Microsoft Learn. (2025, November 18). WHILE (Transact-SQL) - SQL Server.
[Link]
sql?view=sql-server-ver17
[7] Codecademy. (2025, January 12). SQL | Loops.
[Link]