CREATE PROCEDURE - MySQL

Overview

The CREATE PROCEDURE command allows the creation of stored procedures in MySQL. Stored procedures are reusable units of code that encapsulate frequently executed tasks, enhancing code reusability and simplifying maintenance. They are ideal for operations involving complex or frequently executed SQL statements.

Syntax

CREATE PROCEDURE procedure_name [(parameter_list)]
BEGIN
    -- procedure body
END;

Required Arguments:

  • procedure_name: The name of the stored procedure.

Optional Arguments:

  • parameter_list: A comma-separated list of parameters, each specified as parameter_name data_type.

Options/Flags

There are no additional options or flags available for CREATE PROCEDURE.

Examples

Simple Example

CREATE PROCEDURE greet_user (IN username VARCHAR(255))
BEGIN
    -- Display a greeting message for the specified username
    SELECT CONCAT('Hello, ', username, '!');
END;

Example with Parameters

CREATE PROCEDURE calculate_discount (IN subtotal DECIMAL(10,2), IN discount_percentage INT, OUT total_cost DECIMAL(10,2))
BEGIN
    -- Calculate and assign the discounted cost
    SET total_cost = subtotal - (subtotal * discount_percentage / 100);
END;

Common Issues

  • Procedure Already Exists: If a procedure with the same name already exists, an error will be thrown. Ensure that the procedure name is unique.
  • Invalid Syntax: The procedure body must adhere to proper SQL syntax. Any errors in the code will cause the procedure creation to fail.
  • Parameter Types: The data types of the parameters must match the actual values that will be passed during procedure execution. Mismatched data types can lead to errors.

Integration

Stored procedures can be executed using the CALL statement. They can be integrated into scripts or batch files to automate complex tasks or create modular and reusable code.

  • CALL: Executes a stored procedure with specified parameters.
  • DROP PROCEDURE: Drops an existing stored procedure.
  • ALTER PROCEDURE: Modifies an existing stored procedure.

Refer to the MySQL documentation for additional information and examples: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html