Loop Constructs
Caveats of Constructing Loops with SQL
SQL doesn’t have loops in its standard syntax (i.e., SQL itself does not have a native looping construct like you would have in programming languages such as Python, where you can easily use a for loop or while loop to iterate over a collection of data). But there exists procedural extensions like PL/SQL (Oracle) and T-SQL (SQL Server) offer various types of loops.
The SQL WHILE Loop
Important to Note!
SQL supports WHILE loops, where you specify a condition and repeat a block of code until that condition is false. There is no native FOR loop in SQL. However, you can stimulate the same behaviour using a WHILE loop by using cursors.
See also: While Loops in Python
General Syntax
WHILE condition
BEGIN
{...statements...}
END
In Python:
# Initialize counter
counter = 1
# While loop to run until counter is less than or equal to 10
while counter <= 10:
print(f'The counter value is = {counter}') # Print the counter value
counter += 1 # Increment counter by 1
Ouput:
The counter value is = 1
The counter value is = 2
The counter value is = 3
The counter value is = 4
The counter value is = 5
The counter value is = 6
The counter value is = 7
The equivalent would be:
DECLARE @Counter INT -- During declarion, must define datatype
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)
SET @Counter = @Counter + 1
END
| Concept | Explanation | Purpose | Example (T-SQL) |
|---|---|---|---|
| @variable | A local variable in SQL, used to store temporary data during execution. The @ symbol denotes that it is a variable. It is used in procedural code for intermediate values. |
To store data temporarily and use it later in queries, calculations, or logic. | DECLARE @EmployeeID INT; |
| SET | The SET statement is used to assign values to a variable after it has been declared. It is commonly used to initialize or modify the value of a variable. |
To assign a value to a declared variable. | SET @EmployeeID = 101; |
| BEGIN/END | A block used in procedural SQL to define a section of code that should be executed together. This is used to group multiple statements into a single unit. | To encapsulate multiple SQL statements in a single logical block, ensuring they execute together. | BEGIN <statements>; END; |
 |