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
DROP PROCEDURE IF EXISTS `dotnetfunda_3rd`.`LoadThirdPartyArticle`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LoadThirdPartyArticle`(IN artid INT(10),IN status BIT)
select * from 3rdpartyarticles where autoid = artid and active = status order by autoid DESC;
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.
” 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.
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)
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;
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.
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.