26th Feb 2025 Wednesday

What is a Session Variable?

Session variables, also known as user-defined variables, are temporary variables that persist for the duration of a database session — the period during which a user remains connected to the database. These variables are only accessible within the current session and are automatically discarded once the session ends. Unlike table data, session variables are not permanently stored in the database.

Declaring and Assigning Variables

You can declare and assign values to session variables using the SET statement:

SET @my_var = 100;
SELECT @my_var;  -- Retrieves the value of the variable

Alternatively, the SELECT ... INTO syntax allows you to assign the result of a query directly to a variable:

SELECT COUNT(*) INTO @total_employees FROM employees;
SELECT @total_employees;  -- Displays the total number of employees

Using Session Variables in Queries

Session variables can be applied in queries to perform dynamic calculations. For example, to calculate salary increases:

SET @increase_rate = 1.1;

SELECT name, salary, salary * @increase_rate AS new_salary
FROM employees;

Here, the @increase_rate variable stores the salary increase rate, which is applied to each employee’s salary in the query result.

Updating Data with Session Variables

Session variables can also be used in UPDATE statements to set dynamic conditions. For instance, to increase the salaries of employees earning below the average salary:

SET @avg_salary = (SELECT AVG(salary) FROM employees);

UPDATE employees
SET salary = salary * 1.05
WHERE salary < @avg_salary;

This query first calculates the average salary and stores it in @avg_salary, then applies a 5% salary increase to employees earning below that amount.

Inserting Data with Session Variables

You can insert data into another table using session variables combined with INSERT INTO ... SELECT statements. For example, to store information about the highest-paid employees in a separate table:

SET @max_salary = (SELECT MAX(salary) FROM employees);

INSERT INTO high_paid_employees (name, salary)
SELECT name, salary
FROM employees
WHERE salary = @max_salary;

This method inserts data into the target table by selecting matching rows from the source table, without needing explicit VALUES clauses.

Session variables provide a flexible way to store temporary data and simplify query logic, especially in scenarios involving calculations or conditional updates.