How to create a stored procedure in Magento 2.4

1. Create a new module in Magento 2.4, or use an existing module.
2. Create a new file in your module under the Model directory. Name the file according to your procedure, for example: MyProcedure.php.
3. In your new file, create a class that extends \Magento\Framework\Model\AbstractModel. This will allow you to use the Magento 2.4 Object Manager to inject dependencies into your stored procedure class.
4. Create a public method in your class that will execute your stored procedure. This method should be named execute().
5. Use the Magento 2.4 Object Manager to retrieve the database connection, and then execute your stored procedure using SQL.
6. If your stored procedure returns a result set, parse the results and return them as an array or object.

 

Example of a stored procedure that retrieves a list of products by category ID:

<?php

namespace MyVendor\MyModule\Model;

use Magento\Framework\Model\AbstractModel;

use Magento\Framework\App\ResourceConnection;

class MyProcedure extends AbstractModel

{

    protected $_resource;

    public function __construct(

        ResourceConnection $resource

    ) {

        $this->_resource = $resource;

    }

    public function execute($categoryId)

    {

        $connection = $this->_resource->getConnection();

        $result = $connection->query(“CALL my_stored_procedure($categoryId)”);

        $data = [];

        while ($row = $result->fetch()) {

            $data[] = $row;

        }

        return $data;

    }

}

We first injected the ResourceConnection class into our stored procedure class using the Magento 2.4 Object Manager. This allows us to get the database connection. We then define the execute() method, which takes a $categoryId parameter. We execute our stored procedure using SQL, passing in the $categoryId parameter, and then parse the results into an array that we return.