How to Create and Use Stored Procedure in Laravel?

By Hardik Savani May 7, 2021 Category : 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 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

)

)

i hope it can help you..