How to Call a Stored Procedure Using Select Statement in MySQL?

Stored procedures in MySQL are powerful tools for encapsulating SQL logic and enabling reusability. However, executing stored procedures via SELECT statements can provide additional flexibility, especially when integrating their results directly into queries.

This article explores various methods of calling stored procedures using SELECT statements in MySQL, covering scenarios with and without parameters. By understanding these techniques, database developers can enhance the efficiency and versatility of their SQL workflows, ensuring smoother data management processes.

What is a Stored Procedure?

A pre-written SQL query that can be called more than once and still execute the same way is called a Stored Procedure. For example, we can design a stored procedure in an SQL database to insert, select, and update data. Parameters can also be passed to the stored routines.

A stored procedure in MySQL can be called with a SELECT statement to run the procedure and get its output. This is helpful if you want to run a procedure that returns a result set or use the output of a stored process in a query.

You use the CALL statement followed by the procedure name and any required parameters to call a stored procedure using a SELECT statement. You can call the procedure and obtain its results as if they were a table by using the SELECT statement enclosed in parenthesis if the procedure returns a result set.

Here are four typical methods and examples for each:

• Call Stored Procedure Without Parameters
• Using SELECT With Input Parameters
• Using SELECT With Output Parameters
• Using SELECT With Input and Output Parameters

1. Call Stored Procedure Without Parameters

Utilize the CALL statement followed by the procedure name to execute a stored procedure without any input parameters.

Syntax:

--Stored Procedure
DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    SELECT * FROM table_name;
END //
DELIMITER ;

-- Calling the Stored Procedure
CALL procedure_name();

Example: Calling a Stored Procedure Without Parameters

Create a table to store student details.

CREATE TABLE students (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255),
        age INT,
        grade VARCHAR(2)
     );

Insert some sample data into the students table.

INSERT INTO students (name, age, grade) VALUES
('Alice', 20, 'A'),
('Bob', 21, 'B'),
('Charlie', 22, 'C');

Create a stored procedure to fetch student details.

DELIMITER //
CREATE PROCEDURE GetStudentDetails()
BEGIN
    SELECT * FROM students;
END //
DELIMITER ;

Call the stored procedure:

CALL GetStudentDetails();

Output:

Calling a Stored Procedure Without Parameters

Explanation: In this case, the stored procedure doesn’t require any input parameters. The CALL statement is followed by the name of the stored procedure (‘GetStudentDetails’ in this example) and parentheses. The stored procedure ‘GetStudentDetails‘ simply selects all rows from the ‘students‘ table.

2. Using SELECT With Input Parameters

Define input parameters within the CREATE PROCEDURE statement and pass them in the CALL statement.

Syntax:

-- Stored Procedure

DELIMITER //
CREATE PROCEDURE procedure_name(IN _id_name INT)
BEGIN
    SELECT * FROM table_name WHERE id = _id_name;
END //
DELIMITER ;

-- Calling the Stored Procedure

CALL procedure_name(_id);

Example: Calling a Stored Procedure With Input Parameters

Create a table to store employee details.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    department VARCHAR(255)
);

Insert some sample data.

INSERT INTO employees (id, name, age, department)
VALUES 
(1, 'Alice', 30, 'HR'),
(2, 'Bob', 35, 'IT'),
(3, 'Charlie', 40, 'Finance');

Create a stored procedure.

-- Stored Procedure

DELIMITER //
CREATE PROCEDURE GetEmployeeById(IN employeeId INT)
BEGIN
    SELECT * FROM employees WHERE id = employeeId;
END //
DELIMITER ;

Call the stored procedure:

CALL GetEmployeeById(1);

Output:

Calling a Stored Procedure With Input Parameters

Explanation: When the stored procedure requires input parameters, you specify them in the CREATE PROCEDURE statement and use them in the stored procedure’s logic. The CALL statement includes the input parameter’s value (1 in this example) inside the parentheses after the stored procedure’s name.

3. Using SELECT With Output Parameters

Specify output parameters in the stored procedure to return values, accessible through SELECT statements.

Syntax:

-- Stored Procedure

DELIMITER //
CREATE PROCEDURE procedure_name(OUT parameter_name INT)
BEGIN
    SELECT COUNT(*) INTO parameter_name FROM table_name;
END //
DELIMITER ;

-- Calling the Stored Procedure

CALL procedure_name(@count);
SELECT @count;

Example: Calling a Stored Procedure With Output Parameters

Create a table to store employee details

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    department VARCHAR(255)
);

Insert some sample data

INSERT INTO employees (id, name, age, department)
VALUES 
(1, 'Alice', 30, 'HR'),
(2, 'Bob', 35, 'IT'),
(3, 'Charlie', 40, 'Finance');

Create a stored procedure

-- Stored Procedure

DELIMITER //
CREATE PROCEDURE GetEmployeeCount(OUT employeeCount INT)
BEGIN
    SELECT COUNT(*) INTO employeeCount FROM employees;
END //
DELIMITER ;

Call the stored procedure

CALL GetEmployeeCount(@count);
SELECT @count;

Output:

Using SELECT With Output Parameters

Explanation: Output parameters are used to return values from the stored procedure back to the caller.

In this example, the stored procedure ‘GetEmployeeCount’ counts the number of rows in the ‘employees’ table and stores the result in the output parameter ‘employeeCount’. After calling the stored procedure, you can access the value of the output parameter using a SELECT statement (SELECT @count;).

4. Using SELECT With Input and Output Parameters

Combine input and output parameters in the stored procedure to both receive and return specific data.

Syntax:

-- Stored Procedure

DELIMITER //
CREATE PROCEDURE procedure_name(IN input_parameter INT, OUT output_parameter VARCHAR(255))
BEGIN
    SELECT name INTO output_parameter FROM table_name WHERE id = input_parameter;
END //
DELIMITER ;

-- Calling the Stored Procedure

CALL procedure_name(_id, @name);
SELECT @name;

Example: Calling a Stored Procedure with Both Input and Output Parameters

Create a table to store employee details

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    department VARCHAR(255)
);

Insert some sample data

INSERT INTO employees (id, name, age, department)
VALUES 
(1, 'Alice', 30, 'HR'),
(2, 'Bob', 35, 'IT'),
(3, 'Charlie', 40, 'Finance');

Create a stored procedure

DELIMITER //
CREATE PROCEDURE GetEmployeeNameById(IN employeeId INT, OUT employeeName VARCHAR(255))
BEGIN
    SELECT name INTO employeeName FROM employees WHERE id = employeeId;
END //
DELIMITER ;

Call the stored procedure:

CALL GetEmployeeNameById(1, @name);
SELECT @name;

Output:

Using SELECT With Input and Output Parameters

Explanation: This example demonstrates a stored procedure with both input and output parameters. The input parameter ‘employeeId’ is used to specify which employee’s name to retrieve.

The output parameter ‘employeeName’ stores the name of the employee with the specified ID. After calling the stored procedure, you can access the value of the output parameter using a SELECT statement (SELECT @name;).

Conclusion

In conclusion, utilizing a SELECT Query statement in MySQL to call a stored procedure is a simple process. You can run the stored procedure and obtain any result set it generates by using the CALL keyword followed by the name of the procedure and its parameters.

Complex database processes can be easier to maintain and manage thanks to stored procedures, which provide a means of encapsulating and reusing database functionality. In addition to enabling the database server to execute the stored procedure as efficiently as possible, they can enhance performance by lowering the volume of data transferred between the database server and the client application.