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.

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

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

--

--

--

Software Developer @ mditech.net

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Floating SnackBar

Migrate Containerized Application Workloads to the World’s Dominant and Secure AWS EKS Platform in…

How to crop transparent areas of image?

How to create a bot that passes messages between Gitter and Slack ?

aliases and snippets pt. 1 (aliases)

Cortex Project Updates #96

Raging Bull Casino 75 Free Chip 2020

Raging Bull Casino 75 Free Chip 2020

Self-sizing Custom Table View Cells

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
Madhavendra Dutt

Madhavendra Dutt

Software Developer @ mditech.net

More from Medium

How to manage your Materialize migrations with Laravel Zero?

How to Clone Laravel Project from Git Repository and Run it on Local Machine

Laravel 9: Generating Dummy Database Data using Model Factories

CodeIgniter 4 Query Builder join() Method Explained