Create Stored Procedure using Migration in Laravel 9

Updated at 21-Aug-2022, By samar

Create Stored Procedure using Migration in Laravel 9

Hello everyone, in this post we will look at how to solve "Create Stored Procedure using Migration in Laravel 9" in programming.

How to create and call stored procedure using migration in Laravel 9. I am not able to create a stored procedure and call that procedure using Laravel. Please help me to create the stored procedure.
  • Create stored procedure using migration in Laravel 9

    Create stored procedure

    1. Create migration file using below command.

    php artisan make:migration create_get_post_by_userid_procedure
    

    2. Open created migration file.

    File name will be different.

    database\migrations\2022_08_18_072135_create_get_post_by_userid_procedure.php
    

    3. Add the content to created migration file.

    <?php
    
    use Illuminate\Database\Migrations\Migration;
    use Illuminate\Database\Schema\Blueprint;
    use Illuminate\Support\Facades\Schema;
    
    return new class 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()
        {
            
        }
    };
    

    4. Run migration command to migrate.

    To run all migration you can call php artisan migrate command.

    php artisan migrate
    

    Or

    To Run specific migration file you can specify the path of migration file using –path=

    php artisan migrate --path=/database/migrations/2022_08_18_072135_create_get_post_by_userid_procedure.php
    

    Call stored procedure

    routes\web.php

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

    Output :

    ^ array:5 [▼
      0 => {#313 ▼
        +"id": 11
        +"title": "Labore do commodi et"
        +"slug": null
        +"body": ""
        +"is_shared": 0
        +"parent_id": null
        +"user_id": 1
        +"created_at": "2021-10-13 05:10:23"
        +"updated_at": "2021-10-13 05:10:23"
      }
      1 => {#315 ▼
        +"id": 16
        +"title": "Sit quibusdam qui au"
        +"slug": null
        +"body": "Magni aut et velit e"
        +"is_shared": 0
        +"parent_id": null
        +"user_id": 1
        +"created_at": "2021-10-13 11:22:51"
        +"updated_at": "2021-10-13 11:22:51"
      }
      2 => {#316 ▶}
      3 => {#317 ▶}
      4 => {#318 ▶}
    ]
    

Back to code snippet queries related laravel