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

Verify the output

Create the procedure — With parameter

Verify the output

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.

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

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

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