SQL Lab — Stored Procedures

🖥️

SQL Terminal

📝

Procedure Editor

📅

Scheduler

📌 OBJECTIVES

○Create basic stored procedure

○Add IN parameter (date range)

○Add OUT parameter (total)

○Use IF/THEN logic inside proc

○Call procedure successfully

○Schedule Monday automation

0 / 6 complete

SQL TERMINAL — ops_db

MySQL 8.0 — ops_db Tables: orders, products, reps, regions Task: Automate the Monday weekly report with a stored procedure ──────────────────────────────────────

mysql>

PROCEDURE EDITOR

AUTOMATION SCHEDULER

Scheduled procedure runs — deploy your procedure then register it here.

weekly_report

Not deployed

PENDING

Once deployed, click Schedule to register the Monday automation. Then run:
CALL weekly_report('2024-01-01','2024-01-07',@total);
to test it manually.

MISSION BRIEFING

STORED PROCEDURES — VANTAGE OPERATIONS

SCENARIO

Every Monday morning, the Vantage Operations team manually runs the same 12-step sales report. It takes 40 minutes and often has errors. Your job is to build a stored procedure that accepts a date range, produces the full regional breakdown, calculates total revenue as an OUT parameter, uses IF/THEN logic to flag whether the target was met, and can be scheduled to run automatically.

PROCEDURE STRUCTURE

IN start_date DATE — report period start
IN end_date DATE — report period end
OUT total_revenue DECIMAL — populated by SELECT INTO
SELECT summary with GROUP BY region
IF total > 100000 THEN 'TARGET MET'

COMMANDS

Edit the procedure in the Procedure Editor window
Click CREATE & DEPLOY to create it
SHOW PROCEDURE STATUS WHERE Db = 'ops_db';
CALL weekly_report('2024-01-01', '2024-01-07', @total);
SELECT @total;
DROP PROCEDURE IF EXISTS weekly_report;

TIP

The procedure is pre-written in the editor. Read through it carefully, then deploy it and test with CALL. Once deployed, register it in the Scheduler window to complete the automation objective.

Display Mode