Create and Use Stored Procedure in Laravel

What is a Stored Procedure?

A stored procedure is a set of SQL statements that are stored in a relational database management system after compilation so that it can be reused and shared by multiple programs.

Since procedures are stored on the database server which is faster than a client, you can execute quires faster compared to SQL statements used in a program statement.

The stored procedure is always available as ‘source code’ in the database, it is easy to modify the code in one place.

This tutorial is divided into two parts:

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

For this example, I am using 5.7.24 — MySQL Community Server (GPL) and MySql Workbench. You may check your version with SELECT VERSION(); command.

Start MySql server, open MySql Workbench, and follow the below commands in a new SQL query tab.

Check if you have Create routine privilege by SHOW PRIVILEGES; command.

Select the desired database in which you want to create a procedure by USE dbname; command.

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

Part 2: 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.

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

This is how we create and use stored procedures in Laravel.

Thanks, Amit Nair for your question “How to create a migration for Routines”. I have updated the article.

Connect: Twitter, GitHub, Linkedin, MDITech

You can support by buying a coffee ☕️ here https://www.buymeacoffee.com/mdutt