Loops with T-SQL

Learning Exchange
1

SQL (specifically T-SQL for MS SQL Server) includes procedural logic like WHILE loops, which operate much like their counterparts in languages such as Python. They allow you to repeatedly execute a block of code as long as a specified condition remains true.

This T-SQL snippet, for example, prints an increasing number of stars on each line, starting from a @lowerBound and stopping when it reaches the @upperBound. This basic looping structure can be adapted for various procedural data tasks.

Example

DECLARE @upperBound INT = 20;
DECLARE @lowerBound INT = 1;

-- The loop continues as long as the condition is true
WHILE (@upperBound >= @lowerBound)
BEGIN
    -- Prints a string of stars, repeating it '@lowerBound' times
    PRINT REPLICATE('* ', @lowerBound);
    
    -- Increments the counter to work towards the exit condition
    SET @lowerBound = @lowerBound + 1;
END

T-SQL Syntax

  • DECLARE: Used to create a variable. You must provide a name (starting with @) and declare its specific data type (e.g., INT, VARCHAR, DATETIME).
  • WHILE: This keyword initiates the loop and is followed by the condition that is checked before each iteration. You can certainly use multiple variables in this conditional check.
  • BEGIN: Marks the start of the code block that the WHILE loop will execute.
  • SET: This command is used to assign or update the value of a variable. Here, it’s used to increment the counter, which is essential for preventing an infinite loop.
  • END: Marks the finish of the code block associated with the WHILE loop.

Code Credit: Bill Petrosino

1 Like