Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)0%

Procedure Parameters: IN, OUT, and INOUT

Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)

Advanced14 min read•Updated: 2026-09-12

Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)

A Stored Procedure is a compiled collection of one or more SQL statements and procedural logic stored directly in the database catalog. Instead of sending lengthy SQL text from client applications over the network, the client simply invokes the procedure by name using the CALL statement.

Stored procedures reduce network overhead, centralize business logic, and enforce strict security boundaries.

The Role of the DELIMITER Command

In standard SQL execution, the semicolon (;) tells MySQL to execute the preceding statement immediately. However, stored procedure bodies contain internal statements that also terminate with semicolons.

To prevent the MySQL CLI from executing prematurely when encountering the first internal semicolon, you must temporarily change the statement delimiter using the DELIMITER command:

1-- 1. Change delimiter from ; to //
2DELIMITER //
3 
4-- 2. Define the procedure body containing internal semicolons
5CREATE PROCEDURE procedure_name()
6BEGIN
7    SELECT 'First query' AS msg;
8    SELECT 'Second query' AS msg;
9END //
10 
11-- 3. Restore original delimiter
12DELIMITER ;

Creating Your First Stored Procedure

Let's construct a procedure to generate a quarterly sales summary:

1DELIMITER $$
2 
3CREATE PROCEDURE GetQuarterlySalesSummary()
4BEGIN
5    -- Declare local procedure variables
6    DECLARE total_rev DECIMAL(15, 2);
7    DECLARE total_orders INT;
8 
9    -- Compute aggregated revenue
10    SELECT 
11        COALESCE(SUM(total_amount), 0.00),
12        COUNT(order_id)
13    INTO total_rev, total_orders
14    FROM orders
15    WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
16 
17    -- Output final result set
18    SELECT 
19        total_rev AS last_90_days_revenue,
20        total_orders AS total_orders_placed,
21        ROUND(total_rev / NULLIF(total_orders, 0), 2) AS avg_order_value;
22END $$
23 
24DELIMITER ;

Executing and Managing Stored Procedures

1-- Execute procedure
2CALL GetQuarterlySalesSummary();
3 
4-- Inspect stored procedure DDL code
5SHOW CREATE PROCEDURE GetQuarterlySalesSummary;
6 
7-- View procedure metadata in system catalog
8SELECT routine_name, routine_type, created, last_altered 
9FROM information_schema.routines 
10WHERE routine_schema = 'my_database';
11 
12-- Safely drop a stored procedure
13DROP PROCEDURE IF EXISTS GetQuarterlySalesSummary;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why is the DELIMITER command required when defining stored procedures in MySQL?

Question 2

Which SQL statement is used to execute an existing stored procedure?

Question 3

Where are stored procedure definitions stored within MySQL?

Question 4

Which block structure encapsulates the executable statements of a stored procedure?

Question 5

What statement is used to safely remove an obsolete stored procedure?

Next Lesson

Procedure Parameters: IN, OUT, and INOUT

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SQL Query Anti-Patterns & Best Practices	Procedure Parameters: IN, OUT, and INOUT

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Procedure Parameters: IN, OUT, and INOUT

Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)

Stored procedures reduce network overhead, centralize business logic, and enforce strict security boundaries.

The Role of the DELIMITER Command

To prevent the MySQL CLI from executing prematurely when encountering the first internal semicolon, you must temporarily change the statement delimiter using the DELIMITER command:

1-- 1. Change delimiter from ; to //
2DELIMITER //
3 
4-- 2. Define the procedure body containing internal semicolons
5CREATE PROCEDURE procedure_name()
6BEGIN
7    SELECT 'First query' AS msg;
8    SELECT 'Second query' AS msg;
9END //
10 
11-- 3. Restore original delimiter
12DELIMITER ;

Creating Your First Stored Procedure

Let's construct a procedure to generate a quarterly sales summary:

1DELIMITER $$
2 
3CREATE PROCEDURE GetQuarterlySalesSummary()
4BEGIN
5    -- Declare local procedure variables
6    DECLARE total_rev DECIMAL(15, 2);
7    DECLARE total_orders INT;
8 
9    -- Compute aggregated revenue
10    SELECT 
11        COALESCE(SUM(total_amount), 0.00),
12        COUNT(order_id)
13    INTO total_rev, total_orders
14    FROM orders
15    WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
16 
17    -- Output final result set
18    SELECT 
19        total_rev AS last_90_days_revenue,
20        total_orders AS total_orders_placed,
21        ROUND(total_rev / NULLIF(total_orders, 0), 2) AS avg_order_value;
22END $$
23 
24DELIMITER ;

Executing and Managing Stored Procedures

1-- Execute procedure
2CALL GetQuarterlySalesSummary();
3 
4-- Inspect stored procedure DDL code
5SHOW CREATE PROCEDURE GetQuarterlySalesSummary;
6 
7-- View procedure metadata in system catalog
8SELECT routine_name, routine_type, created, last_altered 
9FROM information_schema.routines 
10WHERE routine_schema = 'my_database';
11 
12-- Safely drop a stored procedure
13DROP PROCEDURE IF EXISTS GetQuarterlySalesSummary;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why is the DELIMITER command required when defining stored procedures in MySQL?

Question 2

Which SQL statement is used to execute an existing stored procedure?

Question 3

Where are stored procedure definitions stored within MySQL?

Question 4

Which block structure encapsulates the executable statements of a stored procedure?

Question 5

What statement is used to safely remove an obsolete stored procedure?

Next Lesson

Procedure Parameters: IN, OUT, and INOUT

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SQL Query Anti-Patterns & Best Practices	Procedure Parameters: IN, OUT, and INOUT

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next