How to Create and Use Stored Procedure in Laravel?

Hello Friends,

Now let's see example of how to create and use stored procedure in laravel. We will talk about laravel migration create stored procedure. you can understand a concept of how to write stored procedure in laravel.

I will give simple example with solution. you will learn how to call mysql 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 and laravel 8 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
        )
)

It will help you....