Create and Use Stored Procedure in Laravel

Image for post
Image for post

What is a Stored Procedure?

Part 1: How to create the Stored Procedure in MySql?

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

Part 2: How to use the Stored Procedure in Laravel?

<?phpnamespace 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?

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;'
);
}

Written by

Facilitator | Freelancer | Software Developer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store