How to Create and Use Stored Procedure in Laravel?

Hi Dev,

Today, i will let you know example of how to create and use stored procedures in laravel. if you have question about laravel migration create stored procedure then i will give simple example with solution. you will learn how to call mysql stored procedure in laravel. you can understand a concept of how to write stored procedure in laravel.

In this example i will create mysql stored procedure using laravel migration and we will call stored procedure using laravel eloquent. you can easily use it with laravel 6, laravel 7, laravel 8, laravel 9, laravel 10 and laravel 11 version.

in this example we will create stored procedure call "get_posts_by_userid" that will return posts of given user id. so let's see bellow simple example:

Create Stored Procedure using Migration:

<?php
  
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
  
class CreatePostProcedure extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $procedure = "DROP PROCEDURE IF EXISTS `get_posts_by_userid`;
            CREATE PROCEDURE `get_posts_by_userid` (IN idx int)
            BEGIN
            SELECT * FROM posts WHERE user_id = idx;
            END;";
  
        \DB::unprepared($procedure);
    }
  
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
         
    }
}

Call Stored Procedure:

Route::get('call-procedure', function () {
  
    $postId = 1;
    $getPost = DB::select(
       'CALL get_posts_by_userid('.$postId.')'
    );
  
    dd($getPost);
      
});

Output:

Array
(
    [0] => stdClass Object
        (
            [id] => 5
            [title] => asasas
            [body] => asasasa
            [created_at] => 2021-05-01 06:00:03
            [updated_at] => 2021-05-01 06:00:03
            [user_id] => 1
        )
    [1] => stdClass Object
        (
            [id] => 6
            [title] => sdfsdf
            [body] => sdfsdfsf
            [created_at] => 2021-05-01 06:14:05
            [updated_at] => 2021-05-01 06:14:05
            [user_id] => 1
        )
)

i hope it can help you..