Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Prepared Statements and Session Variables

Explore how to implement prepared statements and session variables within MySQL stored routines to write dynamic, secure, and efficient SQL queries. Understand parameter binding, query execution, and resource management for advanced interview scenarios.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
-- Set your input variables
SET @email := 'johndoe@example.com';
SET @start := DATE '2025-03-01';
SET @end   := DATE '2025-03-10';
-- Prepare a parameterized statement with placeholders
PREPARE stmt FROM '
  SELECT   c.CustomerID, c.CustomerName, SUM(o.TotalAmount) AS TotalSpent
  FROM     Customers c
  JOIN     Orders o ON o.CustomerID = c.CustomerID
  WHERE    c.Email = ? AND o.OrderDate BETWEEN ? AND ?
  GROUP BY c.CustomerID, c.CustomerName
';
-- Execute using your variables
EXECUTE stmt USING @email, @start, @end;
-- Cleanup
DEALLOCATE PREPARE stmt;

Explanation

The explanation of the solution code is given below:

  • Lines 2–4: Define session-scoped variables @email, @start, and @end. DATE 'YYYY-MM-DD' forms an ANSI date literal that MySQL 8.0 accepts, which avoids string-to-date ambiguity.

  • Lines 7–13: Build the statement text with three positional placeholders ?.

    • Line 8: Select the customer identifier and name, plus the aggregated spend SUM(o.TotalAmount).

    • Line 9: Read from Customers as c.

    • Line 10: Inner join Orders as o on the foreign key Orders.CustomerID. These tables and columns are defined in the provided schema.

    • Line 11: Filter by c.Email = ? and a date range using ...