My first stored procedure for MySQL

My first stored procedure for MySQL - Part 1

I went off stored procedures for a long while. I got pretty good at T-SQL in SQL Server 2000 a few years ago, having written some hundred odd procedures for a single website. The reason I went off them came down to maintenance. I guess, because they are not really ‘connected’ to your app logic, it makes it tricky to refactor stuff and get to grips with exactly what might be calling what. Coupled with our total shift over to MySQL, it means I haven’t written a sproc for a long time.

They do definately have a place though. Stored procedures are great for grouping together logic and SQL for a single operation and keeping network traffic to a minimum. My problem previously was I’d been putting everything in to stored procedures, even single line statements, and it was getting out of hand.

A good example of the type of operation you could consider moving in to a sproc is something I’m working on at the moment for our Simple Web Editor. A navigation builder. The nav builder allows an editor to create and edit their own heirachy of navigation. The functionality required from the database is as follows:

Select and delete the navigation items (This is simple suff, I won’t go in to how to do this, but no stored procedure is required!)
Insert a sibling for an existing item.
Insert a child to an existing item.
Re-order a given branch of the tree/heirachy.

The first MySQL stored procedure tackles the first item. Here’s some pseudo code for it.

Take the ID of an existing navigation item.
Obtain the details of the existing navigation item. The parent item (to get the branch in the tree/heirchy), the current sequence and anything else we might need.
Make a gap for the new item. Move all of the siblings of the existing item which have a higher order sequence up one to make room.
Insert the new item.

To do the above in code using SQL would be possible but a bit of a pain. Here’s how to do it with a MySQL stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `simplecms`.`NavItemsInsertSibling`$$

CREATE PROCEDURE `simplecms`.`NavItemsInsertSibling` (
IN osID INT,
IN osWebsiteKey VARCHAR(50),
IN niNavText VARCHAR(100),
IN niNavUrl VARCHAR(200)
)
BEGIN

/*ni prefix stands for new item*/
DECLARE niID INT;

/*os prefix stands for original sibling*/
DECLARE osNavKey VARCHAR(50);
DECLARE osSequence INT;
DECLARE osParentItemID INT;
DECLARE osPagePath VARCHAR(200);
DECLARE osPageFile VARCHAR(50);

/*Put values of existing item in to variables for later use*/
SELECT NavKey, ParentItemID, PagePath, PageFile, Sequence
INTO osNavKey, osParentItemID, osPagePath, osPageFile, osSequence
FROM editnavitems
WHERE NavItemID = osID
AND WebsiteKey = osWebsiteKey
LIMIT 1;

/* Make room for new item by moving all siblings up one. */
UPDATE editnavitems
SET Sequence = Sequence + 1
WHERE ParentItemID = osParentItemID AND Sequence > osSequence;

/* Now insert the new item */
INSERT INTO editnavitems(ParentItemID, NavKey, WebsiteKey, PagePath, PageFile, NavUrl, NavText, Sequence)
VALUES (osParentItemID, osNavKey, osWebsiteKey, osPagePath, osPageFile, niNavUrl, niNavText, osSequence + 1);

SET niID = LAST_INSERT_ID();
SELECT niID;
END$$

DELIMITER ;

It’s fairly straightforward so I’m not going to explain it line by line. All I will point out is one thing that took me a bit of googling to figure out. Where I’m putting data from the existing nav item into variables you need to use the INTO statement within your SELECT clause.

OKCOOL