How to Call a Stored Procedure Using SELECT Statement in MySQL

In MySQL, stored procedures are an effective technique of accelerating and automating database management workflows. By storing a complex piece of logic as a reusable procedure, you can reduce the time needed to execute the required actions, minimize errors, and standardize the output. Stored procedures may include multiple SQL statements updating or selecting data in the database, which are invoked by a single CALL statement.

Combined with the SELECT statement, stored procedures allow you to narrow the input or output to values matching the specified criteria. Let's look at the most typical use cases for this practical technique and examine the syntax, examples, best practices and pitfalls to avoid when using stored procedures with the SELECT statement.

Let's look closer at how stored procedures work and how you can use them to enhance the functionality of SELECT statements.

What is a stored procedure in MySQL?

A stored procedure is a precompiled set of SQL statements stored in a database. Within a stored procedure, statements are executed automatically in a predefined order, performing actions, such as updating or selecting data. Typically, stored procedures are used for frequently repeated complex actions, for example, calculating order price discounts, retrieving lists of employees with specified criteria, etc.

Stored procedures can significantly improve the performance of database workflows in more than one way:

• Acceleration of command execution through calling multiple SQL statements with a single statement
• Minimization of the possibility of errors by eliminating the need to manually enter complex sequences of statements
• Standardization of output for multiple users as a result of running SQL statements with validated parameters and in a predefined order
• Strengthening of security and access control by enabling store procedure execution without direct access to the database tables

dbForge Studio for MySQL offers an intuitive visual tool for creating procedures that allows you to build and store executable business logic. The tool validates your syntax as you type and suggests corrections, if necessary.

How are stored procedures different from SQL functions and scripts?

While all of them are intended to process business logic in SQL, stored procedures, SQL functions, and scripts differ in purpose and usage.

Ways to retrieve data from a stored procedure

MySQL supports multiple ways of getting data as a result of executing a stored procedure. Typically, they include a SELECT query that retrieves data from the database tables.

Standard syntax for calling stored procedures

In MySQL, stored procedures are invoked with a CALL statement. Depending on the purpose of the stored procedure, the CALL statement may or may not contain parameters. Let's look at how to use the GUI for MySQL like dbForge Studio to create and execute stored procedures.

The simplest CALL statement only contains the name of the stored procedure to be executed:

CALL procedureName()

For example, if you need to retrieve a complete list of products with their details, you can do it by creating and storing the following procedure:

CREATE PROCEDURE `GetProductDetails` ()
BEGIN
  SELECT * FROM products;
END

Then, whenever you need a current list of products, just execute a CALL statement for the stored procedure:

CALL GetProductDetails()

Calling stored procedures with parameters

A stored procedure can be created with input and/or output parameters. Input parameters allow you to pass values into the procedure during the CALL statement execution and output parameters - to return specified values. When the stored procedure contains both IN and OUT parameters (called an INOUT parameter), it can pass a value, modify and update it, and return the updated value after execution.

Let's look at some practical examples of creating and calling stored procedures with parameters.

Calling a stored procedure with an IN parameter

By passing a value into a stored procedure in the CALL statement, you can make the procedure act in accordance with it. For example, to retrieve product details by its ID, you can create a procedure that requires an IN parameter:

CREATE PROCEDURE `GetProductByID`(IN id INT)
BEGIN
  SELECT * FROM products
  WHERE product_id = id;
END

Calling this procedure with a product ID returns details for the specified product:

CALL GetProductByID (2)

Calling a stored procedure with an OUT parameter

A stored procedure with an OUT parameter returns a value in the response. If, for example, you need to get a total count of products in a table, you can create the following stored procedure using the COUNT function:

CREATE PROCEDURE `GetTotalProductCount`(OUT product_count int)
BEGIN
  SELECT
    COUNT(*) INTO product_count
  FROM products;
END

When calling this stored procedure, declare the @count variable to display the output:

CALL GetTotalProductCount(@count);
SELECT
  @count;

The response contains the total number of products in the table:

Workarounds for SELECT-based stored procedure access

While SELECT statements cannot be used to call stored procedures explicitly, you can still leverage their capability to retrieve data from your database in workflows that involve other methods of organizing and generating data.

Using temporary tables to store results

With this approach, you create a stored procedure that writes results into a temporary table, from where they are requested in a separate SELECT statement during the stored procedure execution. For example, you can use a temporary table to store results of a procedure that calculates the total sales amount per customer:

CREATE PROCEDURE GetCustomerSales()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_customer_sales;
  CREATE TEMPORARY TABLE tmp_customer_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM payment
  GROUP BY customer_id;
END

Then call the procedure adding a SELECT statement that returns all data from the temporary table:

CALL GetCustomerSales();
SELECT
  *
FROM tmp_customer_sales;

The result contains the total sales amount per customer ID:

Using cursors in stored procedures

In SQL, cursors process data row by row rather than returning the entire table. A stored procedure that contains a cursor can fetch the required data from each individual row and return it as a normal or temporary table.

As an example, let's see how to set up a procedure that goes through a table containing customer data and retrieves customers' names one by one.

First, create a table where customers' names are to be written:

CREATE TABLE log_table (
    log_message VARCHAR(255)
);

Then store a procedure including a cursor that reads the table rows one by one and writes customer names into the table you created:

CREATE PROCEDURE LogCustomerNames()
BEGIN
   DECLARE done int DEFAULT 0;
  DECLARE customer_name varchar(100);
  DECLARE cur CURSOR FOR
  SELECT
    last_name
  FROM customer;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;

    read_loop:
  LOOP
    FETCH cur INTO customer_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO log_table (log_message)
      VALUES (CONCAT('Customer name: ', customer_name));
  END LOOP;
  CLOSE cur;
END

By calling this procedure, you get the table populated with customer names selected from the initial table. Add a SELECT statement to have customer names displayed as output:

CALL LogCustomerNames();
SELECT
  *
FROM log_table;

Using views in conjunction with stored procedures

In SQL, a view is a virtual table based on the results of a SELECT query. Its content is dynamic and represents the data from the table(s) the view depends on at the time of query. Views cannot invoke stored procedures directly, but can serve as query definitions that can be updated via a stored procedure updating the underlying table(s).

To leverage the ability of stored procedures and views to encapsulate reusable business logic, you need to create a procedure that updates the table that the view is based on. For example, if you want to have a straightforward way of getting the updated total sales amount, create and store the procedure that refreshes the total payment amount:

CREATE PROCEDURE RefreshCustomerSalesCache()
BEGIN
  DELETE FROM customer_sales_cache;
  INSERT INTO customer_sales_cache (customer_id, total_sales)
  SELECT customer_id, SUM(amount)
  FROM payment
  GROUP BY customer_id;
END

Then create a view that is based on the table updated by the procedure:

CREATE VIEW v_customer_sales AS
SELECT * FROM customer_sales_cache;

Now, call the stored procedure with a SELECT statement that retrieves data from the view. The response contains the total sales amount updated at the time of query.

CALL RefreshCustomerSalesCache();
SELECT
  *
FROM v_customer_sales;

Using functions in conjunction with stored procedures

In SQL, functions are database objects that perform preprogrammed calculations and return a single value. When used within a stored procedure, functions encapsulate the related business logic, automating calculations and populating tables with calculation results.

Let's assume that you need to apply a discount to all products in your inventory. You can do it quickly by storing a function inside a procedure. This function will calculate a discounted price for all items in your products table.

Start with creating a discounted_products table to store calculation results:

CREATE TABLE discounted_products (
  product_name varchar(100),
  discounted_price decimal(10, 2)
);

Then, create a function that calculates a 10% discount for all product prices:

CREATE FUNCTION `GetDiscount` (`price` decimal(10, 2))
RETURNS decimal(10, 2)
DETERMINISTIC
BEGIN
  RETURN price * 0.9;
END

Finally, include the function in a stored procedure that applies it to each product in the table one by one:

CREATE PROCEDURE `ApplyDiscount` ()
BEGIN
  DECLARE done int DEFAULT 0;
  DECLARE p_name varchar(100);
  DECLARE p_price decimal(10, 2);

  DECLARE cur CURSOR FOR
  SELECT
    product_name,
    price
  FROM products;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;

    read_loop:
  LOOP
    FETCH cur INTO p_name, p_price;
    IF done THEN
      LEAVE read_loop;
    END IF;

    INSERT INTO discounted_products (product_name, discounted_price)
      VALUES (p_name, GetDiscount(p_price));
  END LOOP;

  CLOSE cur;
END

When you run this procedure followed by a SELECT statement requesting to retrieve all the data in the discounted_products table, you see discounted prices for all products:

CALL ApplyDiscount();
SELECT
  *
FROM discounted_products;

Using SELECT after CALL at the application level

This approach is based on the same principles as the technique of using temporary tables to store procedure results. The application code (Python, PHP, or other) calls the stored procedure that writes the results in a temporary table. This is followed by a SELECT statement that retrieves data from the temporary table.

How to use SELECT inside a stored procedure

While you cannot use the SELECT statement to call a stored procedure directly, you can use it within a stored procedure to return the result sets you need, as shown in the examples above. In general, any valid SELECT query can be used in a stored procedure and executed with a single CALL statement.

A stored procedure may include more than one SELECT statement that return data from multiple tables. Such a procedure can significantly accelerate workflows by encapsulating complex logic in a single command. For example, if you need to be able to quickly get an overview of the current number of payments you received next to the current number of rentals, you can store the following procedure:

CREATE PROCEDURE `GetTransactions`()
BEGIN
  SELECT
      COUNT(*) AS PaymentCount
    FROM payment;
  SELECT
      COUNT(*) AS RentalCount
    FROM rental;
END

Calling this stored procedure allows you to get a complete count of payments and rentals as separate queries:

Common issues when calling stored procedures

Let's look at the most common issues that you may encounter while working with stored procedures in MySQL and ways to resolve them.

If you are wondering about how to debug MySQL stored procedures, dbForge Studio for MySQL offers user-friendly tools that help you find and correct issues with your procedure syntax on the fly.

How dbForge Studio for MySQL simplifies stored procedure management

dbForge Studio for MySQL provides a convenient and intuitive environment for developers and database administrators by offering a feature-rich visual graphic interface supporting the entire stored procedure flow: creation, editing, debugging, and execution. If you prefer using a GUI as your primary database management environment, dbForge Studio for MySQL has all you need:

• Visual editor with syntax highlighting and code completion features that assist you in building stored procedures
• Integrated debugger that allows you to test and validate stored procedures before deployment
• Procedure execution directly from within the GUI with detailed explanation of errors, if any
• Intuitive procedure management via Database Explorer

If you are planning to implement stored procedures to automate and expedite your workflows, try dbForge Studio for MySQL and enjoy the convenience of procedure creation and management that it offers.

Conclusion

Stored procedures can greatly accelerate the performance of your application and improve the quality of your data. Used together with the SELECT statement that enables precise selection of data from your database, stored procedures become a powerful database management tool. While not directly executable with a SELECT statement, stored procedures can be invoked with a number of other techniques. Choose the one that suits your purpose best and speed up your data management. Download dbForge Studio for MySQL to streamline your database flows and improve their efficiency.

FAQ