How to use SQL Stored Procedure in Laravel

Create the procedure — Without parameter

DELIMITER $$DROP PROCEDURE IF EXISTS GetUsers$$CREATE PROCEDURE  GetUsers() 
BEGIN 
SELECT * FROM users;
END$$DELIMITER ;

Verify the output

CALL GetUsers();

Create the procedure — With parameter

DELIMITER $$DROP PROCEDURE IF EXISTS GetUserByID$$CREATE PROCEDURE  GetUserByID(
IN `uid` BIGINT
) 
BEGIN 
SELECT * FROM users WHERE id = uid;
END$$DELIMITER ;

Verify the output

CALL GetUserByID(5);

How to use the Stored Procedure in Laravel?

In controller use Illuminate\Support\Facades\DB;

Then use DB::select to call the stored procedure in the first parameter, use the second parameter to pass the parameter/s to the stored procedure, if required, in the form of an array.

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
class UserController extends Controller
{
    public function getUsers()
    {
        $allUsers = DB::select('call GetUsers()');
        return $allUsers;
    }
    public function getUserByID()
    {
        $user = DB::select('call GetUserByID(?)',[5]);
        return $user;
    }
}

How to create a migration for procedures?

To achieve this just create a DB::unprepared command in a migration file’s up() function as follows

public function up()
{
    DB::unprepared(
    'CREATE PROCEDURE  GetUsers()
    BEGIN
    SELECT * FROM users;
    END;'
    );
    DB::unprepared(
    'CREATE PROCEDURE  GetUserByID(
    IN `uid` BIGINT
    )
    BEGIN
    SELECT * FROM users WHERE id = uid;
    END;'
    );
}
public function down()
{
    DB::unprepared(
    'DROP PROCEDURE IF EXISTS GetUsers;'
    );
    DB::unprepared(
    'DROP PROCEDURE IF EXISTS GetUserByID;'
    );
}

Tagged in : Php, Laravel

SQL Stored Procedure in Laravel.

How to use SQL Stored Procedure in Laravel

How to use the Stored Procedure in Laravel?

How to create a migration for procedures?

Dario L. Mindoro