Variables and SET statements in SQL - Step-by-Step Execution

Concept Flow - Variables and SET statements

Declare Variable

↓

Assign Value with SET

↓

Use Variable in Query or Logic

↓

Optionally Reassign with SET

↓

End or Use Variable Result

This flow shows how you declare a variable, assign it a value using SET, use it in queries or logic, and optionally change its value again.

Execution Sample

SQL

DECLARE @count INT;
SET @count = 5;
SET @count = @count + 3;
SELECT @count AS Result;

This code declares a variable, sets it to 5, adds 3, then selects the final value.

Execution Table

Step	Action	Variable	Value	Output/Result
1	Declare variable @count	@count	NULL	No output
2	SET @count = 5	@count	5	No output
3	SET @count = @count + 3	@count	8	No output
4	SELECT @count AS Result	@count	8	Result = 8
5	End of script	-	-	Execution stops

💡 Script ends after SELECT statement returns the variable value.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
@count	NULL	5	8	8

Key Moments - 3 Insights

Why is the variable @count NULL right after declaration?

When declared, variables have no value until SET assigns one, as shown in step 1 and 2 of the execution_table.

What happens if you try to use @count before SET assigns a value?

It will be NULL or cause an error depending on context, because variables start without a value until SET assigns one (see step 1).

Can you change the value of a variable after it is set?

Yes, you can reassign it with SET as shown in step 3 where @count changes from 5 to 8.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of @count after step 2?

A NULL

B 8

C 5

D 0

💡 Hint

Check the 'Value' column in row for step 2 in execution_table.

At which step does @count get its first assigned value?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for the first SET statement assigning a value in execution_table.

If you remove step 3, what would be the output at step 4?

A 5

B NULL

C 8

D Error

💡 Hint

Without step 3, @count remains as assigned in step 2; check variable_tracker for values.

Concept Snapshot

DECLARE variable_name datatype;  -- creates a variable
SET variable_name = value;        -- assigns or changes value
Variables start NULL until SET assigns a value
Use variables in queries or logic by referencing their name
You can reassign variables multiple times with SET

Full Transcript

This lesson shows how to use variables in SQL. First, you declare a variable with DECLARE, which creates it but does not give it a value yet. Then you assign a value using SET. You can change the value later by using SET again. Finally, you can use the variable in queries or logic. The example code declares @count, sets it to 5, adds 3 to make 8, and then selects the result. The execution table shows each step and how the variable changes. Beginners often wonder why the variable is NULL at first or if they can change it later. The answer is yes, you must assign it before use, and you can reassign it anytime. The quiz questions help check understanding of when and how the variable changes.

Practice

1. What is the purpose of using variables in SQL with DECLARE and SET statements?

easy

A. To store temporary values that can be used later in the query

B. To permanently save data in the database

C. To create new tables automatically

D. To delete rows from a table

Solution

1. Step 1: Understand variable usage in SQL

   Variables declared with DECLARE hold temporary values during query execution.

2. Step 2: Identify the purpose of SET

   SET assigns values to these variables for use later in the query.

3. Final Answer:

   To store temporary values that can be used later in the query -> Option A

4. Quick Check:

   Variables = Temporary storage [OK]

Hint: Variables hold temporary data during query execution [OK]

Common Mistakes:

• Thinking variables save data permanently
• Confusing variables with table creation
• Assuming variables delete data

2. Which of the following is the correct syntax to declare an integer variable named @count and set its value to 10 in SQL?

easy

A. DECLARE @count INT; SET @count = 10;

B. SET @count INT; DECLARE @count = 10;

C. DECLARE @count = 10 INT;

D. SET @count = 10 INT;

Solution

1. Step 1: Review correct variable declaration syntax

   In SQL, DECLARE @variable_name TYPE; declares a variable.

2. Step 2: Assign value using SET

   SET @variable_name = value; assigns the value.

3. Final Answer:

   DECLARE @count INT; SET @count = 10; -> Option A

4. Quick Check:

   DECLARE then SET = correct syntax [OK]

Hint: Declare first, then set value with SET [OK]

Common Mistakes:

• Using SET before DECLARE
• Mixing declaration and assignment in wrong order
• Incorrect placement of data type

3. Consider the following SQL code:

DECLARE @x INT;
SET @x = 5;
SET @x = @x + 3;
SELECT @x;

What will be the output of the SELECT statement?

medium

A. 5

B. 8

C. 3

D. NULL

Solution

1. Step 1: Initialize variable @x

   @x is declared as integer and set to 5.

2. Step 2: Update @x by adding 3

   @x = 5 + 3 = 8.

3. Final Answer:

   8 -> Option B

4. Quick Check:

   5 + 3 = 8 [OK]

Hint: Add values stepwise to variable [OK]

Common Mistakes:

• Forgetting to update variable value
• Assuming variable resets after SET
• Confusing initial and updated values

4. Identify the error in the following SQL code:

DECLARE @total INT
SET total = 100;
SELECT @total;

medium

A. Missing semicolon after DECLARE statement

B. SELECT statement syntax is incorrect

C. Variable name missing '@' in SET statement

D. Variable @total not declared

Solution

1. Step 1: Check variable declaration

   @total is declared correctly with DECLARE @total INT.

2. Step 2: Check SET statement

   SET statement uses total without '@', which is incorrect; it should be @total.

3. Final Answer:

   Variable name missing '@' in SET statement -> Option C

4. Quick Check:

   Variable names need '@' prefix [OK]

Hint: Always use '@' before variable names [OK]

Common Mistakes:

• Omitting '@' in variable usage
• Forgetting semicolons (usually optional)
• Misreading SELECT syntax

5. You want to calculate the total price for 3 items each costing 15, 20, and 25 using variables. Which SQL code correctly declares variables, sets their values, and calculates the total?

hard

A. DECLARE @item1 INT = 15, @item2 INT = 20, @item3 INT = 25; SET @total = @item1 + @item2 + @item3; SELECT @total;

B. DECLARE @total INT; SET total = 15 + 20 + 25; SELECT @total;

C. SET @item1 = 15; SET @item2 = 20; SET @item3 = 25; SET @total = @item1 + @item2 + @item3; SELECT @total;

D. DECLARE @item1 INT; DECLARE @item2 INT; DECLARE @item3 INT; DECLARE @total INT; SET @item1 = 15; SET @item2 = 20; SET @item3 = 25; SET @total = @item1 + @item2 + @item3; SELECT @total;

Solution

1. Step 1: Declare all variables before use

   Variables @item1, @item2, @item3, and @total must be declared first.

2. Step 2: Assign values and calculate total

   Assign each item price with SET, then sum them into @total.

3. Step 3: Select the total value

   Use SELECT to output the total price.

4. Final Answer:

   DECLARE @item1 INT; DECLARE @item2 INT; DECLARE @item3 INT; DECLARE @total INT; SET @item1 = 15; SET @item2 = 20; SET @item3 = 25; SET @total = @item1 + @item2 + @item3; SELECT @total; -> Option D

5. Quick Check:

   Declare then set all variables before calculation [OK]

Hint: Declare all variables first, then assign and calculate [OK]

Common Mistakes:

• Assigning values without declaring variables
• Trying to declare and assign in one line incorrectly
• Missing variable declarations