How to create Stored Procedures in MySQL?

Ksuresh
Posted by in Others category on for Beginner level | Points: 250 | Views : 13137 red flag

In this Article, we are going to understand how to work with Stored Procedures in MySQL and also learn Pagination through MySQL stored Procedure

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Ksuresh
Full Name: suresh reddy
Member Level: Starter
Member Status: Member
Member Since: 3/24/2011 3:29:04 AM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Hmanjarawala on: 12/13/2011 | Points: 25
Hi suresh,
I don't know much about MySql, but its good document for beginners

Login to post response

Comment using Facebook(Author doesn't get notification)