How to Create Stored Procedure in MySQL.

It is a segment of declarative statements stored inside the database. It can be invoked by triggers and other stored procedures. In other words,A stored procedure is a SQL code that we can save, so the code can be reused again and again.

Syntax:

CREATEPROCEDUREprocedure_name
AS
sql_statement
GO;

DROP PROCEDURE IF EXISTS `get_subcategory_by_catid`;

delimiter ;;

CREATE PROCEDURE `get_subcategory_by_catid` (IN idxint)

BEGIN

SELECT id, parent_id, title, slug, created_at FROMcategory WHERE parent_id = idx AND status = 1 ORDER BY title;

END

;;

delimiter ;

You can execute this code directly with MySql Query or phpmyadmin. Now you have Created a stored procedure in MySql.

How to Create Stored Procedure in Laravel with QueryBuilder.

$procedure = “

CREATE PROCEDURE `get_subcategory_by_catid`(idxint)

BEGIN

// Your SP here

END

“;

DB::unprepared(“DROP procedure IF EXISTS get_subcategory_by_catid”);

DB::unprepared($procedure);

How to Call Stored Procedure in Laravel.

$getSubCategories = DB::select(

‘CALL get_subcategory_by_catid(‘.$item->category_id.’)’

);

MySQL stored procedures advantages

• Easy to use:Stored proceduresare easy to use.
• Performance :It helps to increase the performance or speed up of the applications.
• Reduce traffic between application and database server: It helps reduce the traffic between application and database server because instead of sending multiple SQL requests, the application has to send only the name and parameters(if required) of the stored procedure.
• Reusable: Stored procedures are transparent to any applications and reusable. Itexposes the database interface to all applications so that the user doesnot have to develop methods that are supported in stored procedures.
• Secure: Stored procedures are safe. The database administrator can grant selected permissions to applications that access stored procedures in the database without giving anylimited permissions on the underlying database tables.

We have a collections of Best Interview Questions and Answers that helps you to crack your future interviews