Loops

• 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)
  

• Assinging values to variables

  Use SET to set a value to the variable

        SET @Snack = 'Cookies'
  

  Show the value

        SELECT @Snack
  

• WHILE Loops

  • WHILE evalues 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

        -- 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 the 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
  

        -- Declare ctr as an integer
        DECLARE @ctr INT
        -- Assign 1 to cr
        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
  

• Derived Tables

  • Query which is treated like a temporary table
  • Always contained within the main query
  • They are specified in the FROM clause
  • Can contain intermediate calculations to be used the main query or different joins than in the main query

        SELECT
            a.* 
        FROM Kidney a
        JOIN (
            SELECT
                AVG(Age) AS AverageAge
            FROM Kidney
        ) b
        ON a.Age = b.AverageAge
  

• Common Table Expressions (CTE)

  A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query. Much like a derived table, the result of a CTE is not stored and exists only for the duration of the query. This article will focus on non-recurrsive CTEs.

  Syntax:

        -- CTE definitions start with the keyword WITH
        -- Followed by the CTE names and the columns it contains
        WITH CTEName (Col1, Col2)
        AS
        -- Defint the CTE Query
        (
        -- The two columns from the definition above
            SELECT 
                Col1, 
                Col2
            FROM TableName
        )
  

        -- Create a CTE to get the Maximum BloodPressure by Age
        WITH BloodPressueAge(Age, MaxBloodPressue)
        AS 
        (
            SELECT
                Age, 
                MAX(BloodPressure) AS MaxBloodPressure
            FROM Kidney
            GROUP BY Age
        )
  
        -- Create a query to use the CTE as a table
        SELECT
            a.Age,
            MIN(a.BloodPressure),
            b.MaxBloodPressure
        FROM Kidney a
        -- Join the CTE with the table
        JOIN BloodpressureAge b
            ON a.Age = b.Age
        GROUP BY a.Age, b.MaxBloodPressure