Scribd
Upload
46 views17 pages

WHILE Loops: Ginger Grant

While loops in T-SQL evaluate a true or false condition. Code inside the loop will continue to execute until the condition is no longer true. The WHILE keyword introduces the condition, which is followed by the BEGIN and END keywords to bracket the code inside the loop. BREAK exits the loop early, while CONTINUE skips the rest of the current loop iteration. Common table expressions (CTEs) in T-SQL allow temporary results to be stored and referenced within the same statement. CTEs are defined with the WITH keyword and given a name and column list, followed by a SELECT query. They can then be referenced in the FROM clause like a table for further processing or joining with other tables in the main query

Uploaded by

anubhav582
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
46 views17 pages

WHILE Loops: Ginger Grant

While loops in T-SQL evaluate a true or false condition. Code inside the loop will continue to execute until the condition is no longer true. The WHILE keyword introduces the condition, which is followed by the BEGIN and END keywords to bracket the code inside the loop. BREAK exits the loop early, while CONTINUE skips the rest of the current loop iteration. Common table expressions (CTEs) in T-SQL allow temporary results to be stored and referenced within the same statement. CTEs are defined with the WITH keyword and given a name and column list, followed by a SELECT query. They can then be referenced in the FROM clause like a table for further processing or joining with other tables in the main query

Uploaded by

anubhav582
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 17

WHILE loops

I N T E R M E D I AT E S Q L S E R V E R

Ginger Grant
Instructor
Using variables in T-SQL
Variables are needed to set values DECLARE @variablename data_type
Must start with the character @

INTERMEDIATE SQL SERVER


Variable data types in T-SQL
VARCHAR(n) : variable length text eld

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

INTERMEDIATE SQL SERVER


Declaring variables in T-SQL
-- Declare Snack as a VARCHAR with length 10
DECLARE @Snack VARCHAR(10)

INTERMEDIATE SQL SERVER


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

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

INTERMEDIATE SQL SERVER


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

INTERMEDIATE SQL SERVER


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

INTERMEDIATE SQL SERVER


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

INTERMEDIATE SQL SERVER


Let's practice!
I N T E R M E D I AT E S Q L S E R V E R
Derived tables
I N T E R M E D I AT E S Q L S E R V E R

Ginger Grant
Instructor
What are Derived tables?
Query which is treated like a temporary table

Always contained within the main query

They are speci ed in the FROM clause

Can contain intermediate calculations to be used the main query or different joins than in the main
query

INTERMEDIATE SQL SERVER


Derived tables in T-SQL
SELECT a.* FROM Kidney a
-- This derived table computes the Average age joined to the actual table
JOIN (SELECT AVG(Age) AS AverageAge
FROM Kidney) b
ON a.Age = b.AverageAge

INTERMEDIATE SQL SERVER


Let's practice!
I N T E R M E D I AT E S Q L S E R V E R
Common Table
Expressions
I N T E R M E D I AT E S Q L S E R V E R

Ginger Grant
Instructor
CTE syntax
-- CTE definitions start with the keyword WITH
-- Followed by the CTE names and the columns it contains
WITH CTEName (Col1, Col2)
AS
-- Define the CTE query
(
-- The two columns from the definition above
SELECT Col1, Col2
FROM TableName
)

INTERMEDIATE SQL SERVER


CTEs in T-SQL
-- Create a CTE to get the Maximum BloodPressure by Age
WITH BloodPressureAge(Age, MaxBloodPressure)
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

INTERMEDIATE SQL SERVER


Let's practice!
I N T E R M E D I AT E S Q L S E R V E R

You might also like