How to create Stored Procedures in MySQL?

Stored Procedures are precompiled queries these stored procedures improves security and execution performance of database client server applications.

Here, as seen below we have database ’MyDatabase’ having a table 3rdpartyarticles .The columns of table can also be seen below. I am using SQLyog –MySQL GUI.

To create a stored procedure, please right click on “Stord Procs” in your database and  select "create stored procedure" and it prompts you to enter  the name for the stored procedure.

Stored Procedure  with Parameters

DELIMITER $$
DROP PROCEDURE IF EXISTS `dotnetfunda_3rd`.`LoadThirdPartyArticle`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LoadThirdPartyArticle`(IN artid INT(10),IN status BIT)
BEGIN
select * from 3rdpartyarticles where autoid = artid and active = status order by autoid DESC;
END$$
DELIMITER ;

Execute the stored procedure LoadThirdPartyArticle then only under the “Stored Procs”   the stored procedure  “LoadThirdPartyArticle” name will appear.

Now  to understand how to pass the parameters using Call StoredProcedurenmae(parameter values separated by commas), refer the image below

Execute Call method then you will get output.

Output

” Custom Pagination” in MySQL Stored procedure

We are creating “loadAll”  Stored Procedure where in we are writing query to customize the selection of records from the database table  3rdpartyarticles.

Code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dotnetfunda_3rd`.`LoadAll`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LoadAll`(IN categoryID INT,IN isRecommended BOOL, IN startIndex INT , IN maxRows INT)
BEGIN
PREPARE statement FROM "select  autoid, title, description, source, ThisDateTime, HitCounter, Author from 3rdpartyarticles where CategoryID = ? and isRecommended = ? and active = true order by autoid desc LIMIT ? , ?";
        SET @categoryID := categoryID;
        SET @isRecommended := isRecommended;
        SET @startIndex := startIndex;
        SET @maxRows := maxRows;
        EXECUTE statement USING @categoryID, @isRecommended, @startIndex, @maxRows;
        DEALLOCATE PREPARE statement; 
        END$$
DELIMITER ;

Now execute the above stored Procedure,  then stored procedure “LoadAll” will appear under the “Stored Procs”.

Now pass the parameters using Call method  as shown in  below picture.

 Execute  above Call method then results will appear according to  parameters passed by you.

Output:

Advantages of Stored Procedures

1. Improves security and  query's performance.
2. Consistent, safe data modification
3. Prevent the wasteful Network bandwidth
4.  Stored Procedures  automatically execute at system start-up
5. Execution plan retention and reuse

Don’t  forget  in Stored Procedure Sql Query ends with semicolon.

Hope this article was useful. Do let me know your comment or feedback.

In case you want to learn ASP.NET with Tips and Tricks, I found .NET Tips and Tricks very useful.