WHILE loops

Intermediate SQL Server

Ginger Grant

Instructor

Using variables in T-SQL

Variables are needed to set values

DECLARE @variablename data_type
- Must start with the character @

Variable data types in T-SQL

VARCHAR(n): variable length text field
INT: integer values from -2,147,483,647 to +2,147,483,647
DECIMAL(p ,s) or NUMERIC(p ,s):
- p: total number of decimal digits that will be stored, both to the left and to the right of the decimal point
- s: number of decimal digits that will be stored to the right of the decimal point

Declaring variables in T-SQL

-- Declare Snack as a VARCHAR with length 10
DECLARE @Snack VARCHAR(10)

Assigning values to variables

-- Declare the variable
DECLARE @Snack VARCHAR(10)
-- Use SET a value to the variable
SET @Snack = 'Cookies'
-- Show the value
SELECT @Snack

+--------------------+ 
|(No column name)    | 
+--------------------+
|Cookies             |
+--------------------+

-- Declare the variable
DECLARE @Snack VARCHAR(10)
-- Use SELECT assign a value
SELECT @Snack = 'Candy'
-- Show the value
SELECT @Snack

+--------------------+ 
|(No column name)    | 
+--------------------+
|Candy               |
+--------------------+

WHILE loops

WHILE evaluates a true or false condition
After the WHILE, there should be a line with the keyword BEGIN
Next include code to run until the condition in the WHILE loop is true
After the code add the keyword END
BREAK will cause an exit out of the loop
CONTINUE will cause the loop to continue

WHILE loop in T-SQL (I)

-- Declare ctr as an integer
DECLARE @ctr INT
-- Assign 1 to ctr
SET @ctr = 1

-- Specify the condition of the WHILE loop
WHILE @ctr < 10

    -- Begin the code to execute inside WHILE loop
    BEGIN
       -- Keep incrementing the value of @ctr
       SET @ctr = @ctr + 1
       -- End WHILE loop
    END
-- View the value after the loop
SELECT @ctr

+--------------------+ 
|(No column name)    | 
+--------------------+
|10                  |
+--------------------+

WHILE loop in T-SQL (II)

-- Declare ctr as an integer
DECLARE @ctr INT
-- Assign 1 to ctr
SET @ctr = 1
-- Specify the condition of the WHILE loop
WHILE @ctr < 10
    -- Begin the code to execute inside WHILE loop
    BEGIN
       -- Keep incrementing the value of @ctr
       SET @ctr = @ctr + 1

       -- Check if ctr is equal to 4
       IF @ctr = 4
           -- When ctr is equal to 4, the loop will break
           BREAK
       -- End WHILE loop
    END

Let's practice!

Intermediate SQL Server