Creating Stored Procedures Using CREATE PROCEDURE in MySQL

Course: MySQL Training Course

Topic: Creating Stored Procedures Using CREATE PROCEDURE

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Stored Procedures in MySQL

A stored procedure is a named collection of SQL statements stored in a MySQL database. It can contain one or more SQL operations, including SELECT, INSERT, UPDATE, and DELETE statements, along with variables, conditional statements, loops, and error-handling logic.

Stored procedures are created using the CREATE PROCEDURE statement and executed using the CALL statement. They allow developers to organize database operations into reusable routines rather than repeatedly writing the same SQL statements in different applications.

For example, an application that frequently retrieves employee information can use a stored procedure instead of sending a separately written SQL query every time.

1.1 What Is CREATE PROCEDURE?

CREATE PROCEDURE is a MySQL statement used to define and create a stored procedure. It specifies the procedure name, optional parameters, and SQL statements that execute when the procedure is called.

CREATE PROCEDURE procedure_name()
BEGIN
    SELECT 'Hello, MySQL!';
END;

The above example creates a procedure named procedure_name. It contains a SELECT statement that returns a text value.

To execute the procedure, use:

CALL procedure_name();

1.2 Why Are Stored Procedures Used?

• Reusability: The same database logic can be executed multiple times.
• Maintainability: Related SQL operations can be organized in one routine.
• Consistency: Applications can use the same database logic for common operations.
• Access control: Database administrators can grant EXECUTE permission on a procedure without necessarily granting direct access to every underlying table.
• Automation: Multiple database operations can be performed through a single CALL statement.
• Centralized logic: Common validation and business rules can be maintained in the database.

1.3 Learning Objectives

After studying this topic, you should be able to:

• Understand stored procedures and their purpose.
• Create procedures using CREATE PROCEDURE.
• Understand procedure syntax and delimiters.
• Execute procedures using CALL.
• Pass input parameters to procedures.
• Return values using OUT and INOUT parameters.
• Use variables, conditional statements, and SQL operations inside procedures.
• Handle errors and manage existing procedures.

2. Understanding Stored Procedures

2.1 Stored Procedure vs. Ordinary SQL Query

An ordinary SQL query is written and executed as a statement. A stored procedure is a named routine saved in the database and invoked whenever required.

2.2 Where Are Stored Procedures Stored?

Stored procedures are database objects associated with a particular schema. MySQL maintains their definitions and metadata. You can inspect procedure information through commands such as SHOW PROCEDURE STATUS, SHOW CREATE PROCEDURE, and the INFORMATION_SCHEMA.ROUTINES view.

When a database is dropped, the stored routines associated with that database are also dropped. Procedure definitions should therefore be included in database deployment and backup planning.

2.3 Common Uses of Stored Procedures

• Retrieving employee or customer records.
• Generating sales and financial reports.
• Inserting new customer or order records.
• Updating product prices or inventory quantities.
• Performing related database operations together.
• Calculating totals and summary values.
• Validating inputs before modifying data.
• Supporting controlled database access for applications.

3. CREATE PROCEDURE Syntax

The basic syntax of a MySQL stored procedure is:

CREATE PROCEDURE procedure_name ([parameter_list])
BEGIN
    SQL_statement_1;
    SQL_statement_2;
    SQL_statement_3;
END;

3.1 Understanding the Procedure Name

Choose a descriptive name that explains the procedure's purpose. Examples include get_employee_details, add_customer, update_product_price, and calculate_monthly_sales.

Good naming conventions make procedures easier to identify, maintain, and use.

3.2 Procedures Without Parameters

A procedure does not need parameters if it performs a fixed operation.

DELIMITER //

CREATE PROCEDURE show_message()
BEGIN
    SELECT 'Welcome to MySQL Stored Procedures' AS message;
END //

DELIMITER ;

Execute it with:

CALL show_message();

Expected output:

3.3 Why Use BEGIN and END?

BEGIN and END define a compound statement block. They are useful when a procedure contains several SQL statements or control-flow statements.

DELIMITER //

CREATE PROCEDURE show_database_info()
BEGIN
    SELECT DATABASE() AS current_database;
    SELECT VERSION() AS mysql_version;
END //

DELIMITER ;

When called, this procedure produces two result sets. The client application must support handling multiple result sets if it needs to process both.

4. Understanding DELIMITER in MySQL

When defining a procedure in the MySQL command-line client, the default statement delimiter is a semicolon (;). Procedure bodies also contain semicolons to separate their internal SQL statements. Without changing the delimiter, the client may treat the first internal semicolon as the end of the CREATE PROCEDURE statement.

DELIMITER changes the delimiter recognized by the MySQL command-line client. It is a client command, not an SQL statement executed by the MySQL server. Many database tools manage routine delimiters differently.

4.1 Example Using DELIMITER

DELIMITER //

CREATE PROCEDURE list_departments()
BEGIN
    SELECT department_id, department_name
    FROM departments;
END //

DELIMITER ;

In this example:

• DELIMITER // temporarily changes the client's statement delimiter.
• The semicolon ends the SELECT statement inside the procedure.
• END // marks the end of the complete procedure definition for the client.
• DELIMITER ; restores the standard delimiter.

4.2 Can Another Delimiter Be Used?

Yes. In the MySQL command-line client, delimiters such as // or $$ are commonly used while defining routines.

DELIMITER $$

CREATE PROCEDURE simple_example()
BEGIN
    SELECT 'Procedure created successfully' AS result;
END $$

DELIMITER ;

Use one delimiter consistently throughout the definition. If your database editor has a dedicated routine editor, follow its instructions for executing stored procedure definitions.

5. Creating Your First Stored Procedure

5.1 Example: Displaying All Employees

Assume an employees table contains employee_id, employee_name, department, and salary columns. The following procedure retrieves all employee records.

DELIMITER //

CREATE PROCEDURE get_all_employees()
BEGIN
    SELECT employee_id, employee_name, department, salary
    FROM employees;
END //

DELIMITER ;

5.2 Executing the Procedure

Use the CALL statement to execute the stored procedure.

CALL get_all_employees();

MySQL runs the SELECT statement and returns the employee records. Every time the procedure is called, the query is executed against the underlying table.

5.3 Example: Displaying Employees from a Specific Department

DELIMITER //

CREATE PROCEDURE get_sales_employees()
BEGIN
    SELECT employee_id, employee_name, department
    FROM employees
    WHERE department = 'Sales';
END //

DELIMITER ;

Execute the procedure:

CALL get_sales_employees();

This procedure returns only employees whose department value is Sales. Because the department is hard-coded, the procedure always uses the same department until its definition is changed.

6. Using IN Parameters

An IN parameter passes a value into a stored procedure. It is the default parameter mode in MySQL. Input parameters make procedures more flexible because the same procedure can process different values on different calls.

6.1 Syntax of an IN Parameter

CREATE PROCEDURE procedure_name(
    IN parameter_name data_type
)
BEGIN
    SQL_statements;
END;

6.2 Example: Get Employee by ID

DELIMITER //

CREATE PROCEDURE get_employee_by_id(
    IN p_employee_id INT
)
BEGIN
    SELECT employee_id, employee_name, department, salary
    FROM employees
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

Call the procedure with an employee ID:

CALL get_employee_by_id(101);

Call it again with another ID:

CALL get_employee_by_id(102);

The same stored procedure handles both requests without requiring a separate procedure for each employee.

6.3 Example: Search Products by Category

DELIMITER //

CREATE PROCEDURE get_products_by_category(
    IN p_category VARCHAR(100)
)
BEGIN
    SELECT product_id, product_name, category, price
    FROM products
    WHERE category = p_category;
END //

DELIMITER ;

Example calls:

CALL get_products_by_category('Electronics');
CALL get_products_by_category('Furniture');

6.4 Example: Filter Orders by Minimum Amount

DELIMITER //

CREATE PROCEDURE get_orders_above_amount(
    IN p_min_amount DECIMAL(10,2)
)
BEGIN
    SELECT order_id, customer_id, order_amount, order_date
    FROM orders
    WHERE order_amount >= p_min_amount;
END //

DELIMITER ;

Execute the procedure:

CALL get_orders_above_amount(5000.00);

This returns orders whose order_amount is greater than or equal to the supplied value.

7. Using Multiple Parameters

A stored procedure can accept more than one parameter. This is useful when a query needs multiple conditions, such as a date range, a category and a minimum price, or a customer's ID and order status.

7.1 Example: Search Employees by Department and Minimum Salary

DELIMITER //

CREATE PROCEDURE search_employees(
    IN p_department VARCHAR(100),
    IN p_min_salary DECIMAL(10,2)
)
BEGIN
    SELECT employee_id, employee_name, department, salary
    FROM employees
    WHERE department = p_department
      AND salary >= p_min_salary;
END //

DELIMITER ;

Execute the procedure:

CALL search_employees('IT', 40000.00);

The procedure returns employees in the IT department whose salary is at least 40000.

7.2 Example: Retrieve Orders Within a Date Range

DELIMITER //

CREATE PROCEDURE get_orders_by_date_range(
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    SELECT order_id, customer_id, order_date, order_amount
    FROM orders
    WHERE order_date >= p_start_date
      AND order_date <= p_end_date
    ORDER BY order_date;
END //

DELIMITER ;

Call the procedure:

CALL get_orders_by_date_range('2026-01-01', '2026-01-31');

When designing date-range procedures, confirm that the supplied start date is not later than the end date. If the underlying column is a DATETIME rather than a DATE, use an appropriate range condition so that records late on the final day are not unintentionally excluded.

8. Using OUT Parameters

An OUT parameter returns a value from a procedure to the caller. It is useful when a procedure calculates a count, total, status, or other result that the calling application needs to use.

8.1 Syntax of an OUT Parameter

CREATE PROCEDURE procedure_name(
    OUT output_parameter data_type
)
BEGIN
    -- Assign a value to the output parameter
END;

8.2 Example: Count Employees

DELIMITER //

CREATE PROCEDURE count_employees(
    OUT p_employee_count INT
)
BEGIN
    SELECT COUNT(*)
    INTO p_employee_count
    FROM employees;
END //

DELIMITER ;

Call the procedure using a MySQL user-defined variable:

CALL count_employees(@total_employees);

SELECT @total_employees AS total_employees;

The procedure calculates the number of employee rows and assigns the result to the OUT parameter. The value can then be retrieved through @total_employees.

8.3 Example: Calculate Total Sales

DELIMITER //

CREATE PROCEDURE calculate_total_sales(
    OUT p_total_sales DECIMAL(12,2)
)
BEGIN
    SELECT COALESCE(SUM(order_amount), 0)
    INTO p_total_sales
    FROM orders;
END //

DELIMITER ;

Execute the procedure and read the result:

CALL calculate_total_sales(@sales_total);

SELECT @sales_total AS total_sales;

COALESCE returns zero when SUM() produces NULL, such as when no matching rows exist.

9. Using INOUT Parameters

An INOUT parameter receives an initial value from the caller and can return a modified value. It is useful when a procedure needs to transform or update a value supplied by the caller.

9.1 Example: Increase a Value

DELIMITER //

CREATE PROCEDURE increase_value(
    INOUT p_value INT
)
BEGIN
    SET p_value = p_value + 10;
END //

DELIMITER ;

Call the procedure:

SET @number = 20;

CALL increase_value(@number);

SELECT @number AS updated_number;

Expected output:

9.2 Comparison of IN, OUT, and INOUT

10. Declaring and Using Variables

Stored procedures can declare local variables to hold temporary values during execution. Local variables are declared with DECLARE inside a compound block and before other executable statements in that block.

10.1 Syntax

DECLARE variable_name data_type DEFAULT default_value;

10.2 Example: Store a Count in a Local Variable

DELIMITER //

CREATE PROCEDURE show_employee_count()
BEGIN
    DECLARE v_count INT DEFAULT 0;

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    SELECT v_count AS employee_count;
END //

DELIMITER ;

Execute the procedure:

CALL show_employee_count();

10.3 Local Variables vs. User-Defined Variables

11. Using Conditional Statements in Procedures

MySQL stored procedures support control-flow statements, including IF, ELSEIF, ELSE, and CASE. These statements allow a procedure to execute different logic depending on a condition.

11.1 Example: Check an Employee's Salary

DELIMITER //

CREATE PROCEDURE check_salary(
    IN p_employee_id INT
)
BEGIN
    DECLARE v_salary DECIMAL(10,2) DEFAULT NULL;

    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    IF v_salary IS NULL THEN
        SELECT 'Employee not found or salary is NULL' AS message;
    ELSEIF v_salary >= 60000 THEN
        SELECT 'High salary range' AS message;
    ELSE
        SELECT 'Standard salary range' AS message;
    END IF;
END //

DELIMITER ;

Execute the procedure:

CALL check_salary(101);

This example illustrates conditional logic, but it treats a missing employee and a NULL salary alike. In production code, handle these cases separately if the business rules require different outcomes.

11.2 Example: Use CASE to Classify Orders

DELIMITER //

CREATE PROCEDURE classify_orders()
BEGIN
    SELECT
        order_id,
        order_amount,
        CASE
            WHEN order_amount >= 10000 THEN 'High Value'
            WHEN order_amount >= 5000 THEN 'Medium Value'
            ELSE 'Low Value'
        END AS order_category
    FROM orders;
END //

DELIMITER ;

Call the procedure:

CALL classify_orders();

12. Using INSERT, UPDATE, and DELETE in Stored Procedures

Stored procedures can perform data modification operations. This allows an application to call a routine for common database actions while keeping the operation's SQL definition in the database.

12.1 Example: Insert a New Customer

DELIMITER //

CREATE PROCEDURE add_customer(
    IN p_customer_name VARCHAR(100),
    IN p_city VARCHAR(60),
    IN p_email VARCHAR(150)
)
BEGIN
    INSERT INTO customers (customer_name, city, email)
    VALUES (p_customer_name, p_city, p_email);
END //

DELIMITER ;

Execute the procedure:

CALL add_customer(
    'Aarav Sharma',
    'Mumbai',
    '[email protected]'
);

Make sure the values comply with the table's constraints and that the procedure has the appropriate privileges.

12.2 Example: Update a Product Price

DELIMITER //

CREATE PROCEDURE update_product_price(
    IN p_product_id INT,
    IN p_new_price DECIMAL(10,2)
)
BEGIN
    UPDATE products
    SET price = p_new_price
    WHERE product_id = p_product_id;
END //

DELIMITER ;

Execute the procedure:

CALL update_product_price(101, 2499.00);

For a production system, validate that the price is permitted by the business rules and confirm whether a matching product was updated.

12.3 Example: Delete an Old Order

DELIMITER //

CREATE PROCEDURE delete_order(
    IN p_order_id INT
)
BEGIN
    DELETE FROM orders
    WHERE order_id = p_order_id;
END //

DELIMITER ;

Execute the procedure:

CALL delete_order(101);

Deletion is a potentially destructive operation. In production, verify the target record, foreign-key relationships, authorization, and retention requirements before allowing a procedure to delete data.

13. Error Handling in Stored Procedures

Stored procedures can include handlers to respond to SQL conditions. Error handling is important when a routine performs several operations or needs to return a meaningful response when a statement fails.

13.1 Basic CONTINUE Handler

A CONTINUE handler allows execution to continue after the handled condition. Use it only when continuing is appropriate for the condition.

DELIMITER //

CREATE PROCEDURE example_continue_handler()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        SELECT 'A SQL exception occurred' AS message;

    SELECT 1 / 0 AS calculation_result;
    SELECT 'Execution continued' AS status_message;
END //

DELIMITER ;

The exact warning or error behavior of the calculation depends on the SQL mode and expression. This is a demonstration of handler syntax, not a recommended pattern for ignoring arbitrary database errors.

13.2 EXIT Handler

An EXIT handler terminates execution of the compound block in which it is declared when the specified condition occurs.

DELIMITER //

CREATE PROCEDURE example_exit_handler()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'The procedure encountered an SQL error' AS error_message;
    END;

    SELECT * FROM table_that_does_not_exist;
    SELECT 'This statement is not reached after the error' AS status_message;
END //

DELIMITER ;

This example intentionally references a nonexistent table to demonstrate an exception handler. Do not use intentionally invalid SQL in production procedures.

13.3 Using SIGNAL to Raise an Error

The SIGNAL statement can raise a condition when a business rule is violated.

DELIMITER //

CREATE PROCEDURE validate_product_price(
    IN p_price DECIMAL(10,2)
)
BEGIN
    IF p_price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price cannot be negative';
    ELSE
        SELECT 'Price is valid' AS validation_result;
    END IF;
END //

DELIMITER ;

Test a valid price:

CALL validate_product_price(500.00);

Test an invalid price:

CALL validate_product_price(-10.00);

The invalid call raises a user-defined SQL error.

14. Creating a Stored Procedure with Transaction Logic

A transaction groups database changes so they can be committed together or rolled back when a failure occurs. Stored procedures can use transaction statements, but transaction boundaries must be designed carefully because they interact with the caller's transaction and the storage engine.

14.1 Example: Transfer Money Between Accounts

The following example illustrates a simplified transfer between two bank accounts. It assumes an accounts table with account_id as a primary key and balance as a decimal column. Both accounts must exist, and the operation must be executed with suitable authorization.

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(12,2)
)
BEGIN
    DECLARE v_from_balance DECIMAL(12,2) DEFAULT NULL;
    DECLARE v_to_account_count INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    IF p_amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Transfer amount must be positive';
    END IF;

    IF p_from_account = p_to_account THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Source and destination must differ';
    END IF;

    START TRANSACTION;

    SELECT balance
    INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    SELECT COUNT(*)
    INTO v_to_account_count
    FROM accounts
    WHERE account_id = p_to_account;

    IF v_from_balance IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Source account not found or balance is NULL';
    END IF;

    IF v_to_account_count = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Destination account not found';
    END IF;

    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;
END //

DELIMITER ;

Important: This is a learning example and requires additional production safeguards. For a robust transfer routine, validate balances and account existence under suitable locks, handle NULL values explicitly, consider locking both accounts in a consistent order, and ensure that the procedure's transaction behavior matches the application's transaction policy. The EXIT handler above rolls back on SQL exceptions and rethrows the error with RESIGNAL.

15. Viewing and Managing Stored Procedures

15.1 Display Procedure Status

Use SHOW PROCEDURE STATUS to inspect stored procedures visible to the current account.

SHOW PROCEDURE STATUS;

To filter by database:

SHOW PROCEDURE STATUS
WHERE Db = 'company_db';

15.2 Display the Procedure Definition

Use SHOW CREATE PROCEDURE to inspect the stored SQL definition.

SHOW CREATE PROCEDURE get_all_employees;

15.3 List Stored Routines in a Database

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'company_db'
  AND ROUTINE_TYPE = 'PROCEDURE';

Replace company_db with the name of your database.

15.4 Drop a Stored Procedure

DROP PROCEDURE removes a stored procedure. It does not delete the tables or records used by the procedure.

DROP PROCEDURE IF EXISTS get_all_employees;

Before dropping a procedure, check whether applications, scheduled jobs, or other routines depend on it.

15.5 Altering a Procedure

ALTER PROCEDURE changes certain characteristics of an existing procedure, such as its comment or SQL security characteristics where supported. It is not used to rewrite the procedure's SQL body. To change the body, use an appropriate deployment process to drop and recreate the procedure, or use CREATE OR REPLACE PROCEDURE only if the specific MySQL version supports it. Check your target server's documentation before choosing a replacement workflow.

16. Stored Procedure Security and Permissions

Stored procedure security determines who can execute a routine and whose privileges are used to access underlying database objects. MySQL supports SQL SECURITY DEFINER and SQL SECURITY INVOKER.

16.1 Granting EXECUTE Permission

An administrator can grant a user permission to execute a stored procedure.

GRANT EXECUTE
ON PROCEDURE company_db.get_all_employees
TO 'report_user'@'localhost';

The user must also satisfy the applicable security requirements of the routine and its underlying objects. Use least-privilege permissions and test access with the intended account.

16.2 Security Best Practices

• Grant EXECUTE only to accounts that need the procedure.
• Review whether DEFINER or INVOKER is appropriate for the task.
• Validate parameters before changing important records.
• Use primary keys and suitable WHERE conditions for updates and deletes.
• Avoid building dynamic SQL by concatenating untrusted input.
• Keep database credentials out of procedure bodies and source code.
• Review privileges on underlying tables as well as procedure permissions.
• Document procedures that perform sensitive operations.

17. Common Errors and Troubleshooting

17.1 Troubleshooting Checklist

1. Confirm the correct database is selected.
2. Verify that the procedure exists.
3. Inspect its definition with SHOW CREATE PROCEDURE.
4. Check the parameter names, data types, modes, and argument order.
5. Review the delimiter settings in the SQL client.
6. Run the underlying SQL statements separately when appropriate.
7. Check permissions on the procedure and referenced objects.
8. Test both normal inputs and invalid inputs.
9. Verify affected records before and after data modification.
10. Review transaction and error-handler behavior for multi-step operations.

18. Best Practices for Writing Stored Procedures

• Use descriptive procedure names that clearly explain their purpose.
• Keep procedures focused on a specific task.
• Use parameterized inputs instead of hard-coding values that may change.
• Choose appropriate data types and sizes for parameters.
• Use IN for inputs, OUT for returned values, and INOUT when a value must be passed in and modified.
• Use local variables for intermediate calculations.
• Validate inputs before modifying database records.
• Use explicit column lists instead of SELECT * for stable application interfaces.
• Include error handling when a failure requires special treatment.
• Use transactions when multiple data changes must succeed or fail together.
• Document the purpose, parameters, and expected results of each routine.
• Test stored procedures against realistic data and edge cases.
• Use version control and controlled deployment scripts for routine definitions.
• Review performance with EXPLAIN for important queries.
• Grant only the permissions needed by the procedure and its callers.

19. Practical Project: Employee Management Stored Procedures

19.1 Project Objective

Create a small employee management system using MySQL stored procedures. The procedures will retrieve employee records, add employees, update salaries, and calculate the total employee count.

19.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

19.3 Insert Sample Records

INSERT INTO employees (employee_name, department, salary)
VALUES
('Aarav Sharma', 'IT', 55000.00),
('Meera Patel', 'HR', 45000.00),
('Rohan Verma', 'Finance', 60000.00),
('Priya Singh', 'IT', 65000.00);

19.4 Create a Procedure to Retrieve All Employees

DELIMITER //

CREATE PROCEDURE project_get_all_employees()
BEGIN
    SELECT employee_id, employee_name, department, salary
    FROM employees
    ORDER BY employee_id;
END //

DELIMITER ;

Execute it:

CALL project_get_all_employees();

19.5 Create a Procedure to Retrieve an Employee by ID

DELIMITER //

CREATE PROCEDURE project_get_employee(
    IN p_employee_id INT
)
BEGIN
    SELECT employee_id, employee_name, department, salary
    FROM employees
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

Execute it:

CALL project_get_employee(1);

19.6 Create a Procedure to Update Salary

DELIMITER //

CREATE PROCEDURE project_update_salary(
    IN p_employee_id INT,
    IN p_new_salary DECIMAL(10,2)
)
BEGIN
    IF p_new_salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;

    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

Execute it:

CALL project_update_salary(1, 58000.00);

Verify the change:

SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 1;

19.7 Create a Procedure to Count Employees

DELIMITER //

CREATE PROCEDURE project_employee_count(
    OUT p_total INT
)
BEGIN
    SELECT COUNT(*)
    INTO p_total
    FROM employees;
END //

DELIMITER ;

Execute it:

CALL project_employee_count(@employee_total);

SELECT @employee_total AS employee_total;

19.8 Project Testing Checklist

• Confirm all four procedures are created successfully.
• Retrieve all employee records.
• Search for an existing and a nonexistent employee ID.
• Update a salary and verify the stored value.
• Test that a negative salary is rejected.
• Check that the OUT parameter returns the employee count.
• Inspect each procedure with SHOW CREATE PROCEDURE.
• Review the privileges required by the application's database account.

20. Practice Exercises

1. Create a procedure named get_all_customers that displays all customers.
2. Create a procedure named get_customer_by_id that accepts a customer ID.
3. Create a procedure that lists products belonging to a supplied category.
4. Create a procedure that accepts a minimum price and returns matching products.
5. Create a procedure that counts orders and returns the result through an OUT parameter.
6. Create a procedure that calculates the total value of all orders.
7. Create a procedure that inserts a new product using IN parameters.
8. Create a procedure that updates the department of an employee.
9. Create an INOUT procedure that multiplies a supplied number by two.
10. Create a procedure that raises an error when an input amount is negative.
11. Display the definition of a procedure using SHOW CREATE PROCEDURE.
12. Grant EXECUTE permission on a procedure to a suitable database account.

21. Exercise Solutions

Solution 1: Get All Customers

DELIMITER //

CREATE PROCEDURE get_all_customers()
BEGIN
    SELECT *
    FROM customers;
END //

DELIMITER ;

Solution 2: Get Customer by ID

DELIMITER //

CREATE PROCEDURE get_customer_by_id(
    IN p_customer_id INT
)
BEGIN
    SELECT customer_id, customer_name, city
    FROM customers
    WHERE customer_id = p_customer_id;
END //

DELIMITER ;

Solution 3: Products by Category

DELIMITER //

CREATE PROCEDURE get_products_by_category(
    IN p_category VARCHAR(100)
)
BEGIN
    SELECT product_id, product_name, category, price
    FROM products
    WHERE category = p_category;
END //

DELIMITER ;

Solution 4: Products Above a Minimum Price

DELIMITER //

CREATE PROCEDURE get_products_above_price(
    IN p_min_price DECIMAL(10,2)
)
BEGIN
    SELECT product_id, product_name, price
    FROM products
    WHERE price >= p_min_price;
END //

DELIMITER ;

Solution 5: Count Orders

DELIMITER //

CREATE PROCEDURE get_order_count(
    OUT p_total_orders INT
)
BEGIN
    SELECT COUNT(*)
    INTO p_total_orders
    FROM orders;
END //

DELIMITER ;

Call it:

CALL get_order_count(@order_total);
SELECT @order_total;

Solution 6: Calculate Total Order Value

DELIMITER //

CREATE PROCEDURE get_total_order_value(
    OUT p_total_value DECIMAL(12,2)
)
BEGIN
    SELECT COALESCE(SUM(order_amount), 0)
    INTO p_total_value
    FROM orders;
END //

DELIMITER ;

Solution 7: Insert a Product

DELIMITER //

CREATE PROCEDURE add_product(
    IN p_product_name VARCHAR(100),
    IN p_category VARCHAR(100),
    IN p_price DECIMAL(10,2)
)
BEGIN
    IF p_price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;

    INSERT INTO products (product_name, category, price)
    VALUES (p_product_name, p_category, p_price);
END //

DELIMITER ;

Solution 8: Update Employee Department

DELIMITER //

CREATE PROCEDURE update_employee_department(
    IN p_employee_id INT,
    IN p_department VARCHAR(100)
)
BEGIN
    UPDATE employees
    SET department = p_department
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

Solution 9: Multiply an INOUT Value by Two

DELIMITER //

CREATE PROCEDURE double_value(
    INOUT p_value INT
)
BEGIN
    SET p_value = p_value * 2;
END //

DELIMITER ;

Call it:

SET @number = 15;
CALL double_value(@number);
SELECT @number AS doubled_value;

Solution 10: Validate an Amount

DELIMITER //

CREATE PROCEDURE validate_amount(
    IN p_amount DECIMAL(10,2)
)
BEGIN
    IF p_amount < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Amount cannot be negative';
    ELSE
        SELECT 'Valid amount' AS result;
    END IF;
END //

DELIMITER ;

Solution 11: Display Procedure Definition

SHOW CREATE PROCEDURE get_all_customers;

Solution 12: Grant Execution Permission

GRANT EXECUTE
ON PROCEDURE company_db.get_all_customers
TO 'app_user'@'localhost';

Replace the database name and account with values appropriate for your environment.

22. MySQL Interview Questions and Answers

Q1. What is a stored procedure?

A stored procedure is a named routine stored in a MySQL database that can contain one or more SQL statements and control-flow logic.

Q2. Which statement creates a stored procedure?

The CREATE PROCEDURE statement creates a stored procedure.

Q3. How do you execute a stored procedure?

Use the CALL statement followed by the procedure name and its arguments.

CALL get_all_employees();

Q4. Why is DELIMITER used?

In the MySQL command-line client, DELIMITER changes the client-side statement terminator so semicolons inside the procedure body do not prematurely end the full definition.

Q5. What is the purpose of BEGIN and END?

They define a compound statement block containing multiple SQL statements and potentially control-flow logic.

Q6. What are IN, OUT, and INOUT parameters?

IN passes data into a procedure, OUT returns a value, and INOUT accepts an initial value that can be modified and returned.

Q7. Can a procedure contain multiple SQL statements?

Yes. A stored procedure can contain multiple statements inside a compound block.

Q8. Can stored procedures modify data?

Yes. They can execute INSERT, UPDATE, and DELETE statements when the applicable permissions and constraints allow those operations.

Q9. What is the difference between a stored procedure and a stored function?

A procedure is invoked using CALL and can return values through OUT or INOUT parameters and result sets. A stored function is used as an expression and must declare a return type.

Q10. How can you inspect a stored procedure's definition?

Use SHOW CREATE PROCEDURE procedure_name.

Q11. How can you remove a stored procedure?

Use DROP PROCEDURE, optionally with IF EXISTS.

Q12. Can a procedure accept multiple parameters?

Yes. It can accept multiple parameters of suitable MySQL data types and modes.

Q13. What is a local variable in a stored procedure?

A local variable is declared with DECLARE inside a compound block and is used to hold intermediate values during execution.

Q14. How do you handle SQL errors in a procedure?

MySQL provides condition handlers such as CONTINUE and EXIT handlers. SIGNAL can raise a user-defined error, and RESIGNAL can propagate a handled condition.

Q15. What does SQL SECURITY DEFINER mean?

It means the procedure's underlying object privileges are checked using the definer's security context. It is the default security mode.

Q16. What does SQL SECURITY INVOKER mean?

It means underlying object privileges are checked using the calling user's security context.

Q17. Do stored procedures always make applications faster?

No. Their benefits include reuse and centralized database logic, but actual performance depends on the queries, data, indexes, application architecture, and execution plan.

Q18. What privilege is needed to create a stored procedure?

The account generally needs the CREATE ROUTINE privilege for the applicable schema, subject to the server's configuration and security rules.

Q19. Can a procedure return a result set?

Yes. A SELECT statement inside a procedure can return a result set to the client.

Q20. What is the purpose of an OUT parameter?

An OUT parameter returns a calculated or retrieved value to the caller, such as an employee count or total sales amount.

23. Quick Revision Checklist

• CREATE PROCEDURE defines a stored procedure.
• CALL executes a stored procedure.
• BEGIN and END group statements into a compound block.
• DELIMITER is a MySQL client command used when defining routines with internal semicolons.
• IN parameters pass values into a procedure.
• OUT parameters return values to the caller.
• INOUT parameters pass values in and return modified values.
• DECLARE defines local variables within a stored-program block.
• IF, ELSEIF, ELSE, and CASE support conditional logic.
• Handlers can respond to SQL conditions and exceptions.
• SIGNAL can raise a user-defined error.
• SHOW CREATE PROCEDURE displays the procedure definition.
• DROP PROCEDURE removes a stored procedure.
• EXECUTE privileges control who may run a procedure.
• Validate parameters and test error-handling logic before deploying routines.

24. Conclusion

Creating stored procedures using CREATE PROCEDURE is an important MySQL skill. Stored procedures help developers organize SQL statements into reusable database routines and support operations such as data retrieval, insertion, updating, calculations, and validation.

By learning procedure syntax, delimiters, parameter modes, local variables, conditional statements, error handlers, and permissions, you can develop database routines that are easier to maintain and safer to use. Always test procedure behavior with valid and invalid inputs, verify the permissions required by callers, and use transactions carefully when multiple changes must be handled together.

Explore the course: MySQL Training Course at JustAcademy

Register for a demo: Book a MySQL Course Demo