WHILE loops in procedures in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a WHILE loop in SQL procedures?

A WHILE loop repeatedly executes a block of SQL code as long as a specified condition is true.

beginner

How do you start a WHILE loop in a SQL procedure?

You start with the keyword WHILE followed by a condition, then the code block inside BEGIN and END.

beginner

Why is it important to change the condition inside a WHILE loop?

To avoid an infinite loop, you must update the condition inside the loop so it eventually becomes false.

intermediate

Example: What does this WHILE loop do? DECLARE @i INT = 1; WHILE @i <= 3 BEGIN PRINT @i; SET @i = @i + 1; END

It prints the numbers 1, 2, and 3 one by one, increasing @i by 1 each time until @i is greater than 3.

intermediate

Can WHILE loops be nested inside other WHILE loops in SQL procedures?

Yes, you can put a WHILE loop inside another WHILE loop to repeat complex tasks multiple times.

What keyword starts a WHILE loop in a SQL procedure?

A LOOP

B REPEAT

C FOR

D WHILE

The WHILE keyword is used to start a WHILE loop in SQL procedures.

What happens if the condition in a WHILE loop never becomes false?

A The loop stops automatically

B The loop runs forever (infinite loop)

C The procedure throws an error

D The loop runs once

If the condition never becomes false, the WHILE loop runs forever, causing an infinite loop.

Where do you put the code that repeats inside a WHILE loop?

A Between BEGIN and END

B After the WHILE keyword only

C Outside the procedure

D Inside a SELECT statement

The repeated code goes inside BEGIN and END blocks within the WHILE loop.

Which of these is a valid condition for a WHILE loop?

A SET @count = 0

B PRINT @count

C @count < 10

D DECLARE @count INT

Conditions in WHILE loops are boolean expressions like '@count < 10'.

Can you use a WHILE loop to repeat a task a fixed number of times?

A Yes, by controlling the loop condition

B No, WHILE loops run only once

C Only if you use a FOR loop inside

D Only in SELECT statements

You can repeat a task a fixed number of times by setting the WHILE loop condition accordingly.

Explain how a WHILE loop works inside a SQL procedure and why updating the loop condition is important.

Think about what happens if the condition never changes.

Describe a simple example of a WHILE loop in a SQL procedure that counts from 1 to 5.

Start with a variable set to 1 and increase it until 5.