Articles: Stored procedures in MySQL 5.0

Stored Procedures, hereby SPs, were a long awaitened feature in MySQL on my behalf. It allow for more work to be done in the DBMS and allow for fewer queries between PHP and MySQL. When I say I want a recursive SP I mean a structure such as:

The idea behind this is that C/C++ belong to Development, which belong to Reference which belong to Root. Hence I want to build a hierartical tree view of it for easy navigation between the four levels.

I could have written this in standard SQL as:

SELECT *,'1' AS a FROM links_categories WHERE id = 52
UNION SELECT *,'2' FROM links_categories WHERE id = 12
UNION SELECT *,'3' FROM links_categories WHERE id = 4
UNION SELECT 0,0,'Root','4' ORDER BY a DESC;

But that bring up several problems, first of all it would have to be done by PHP, and efficiently be 4 queries instead of 1. On the next page we're going to show how it is doen by a MySQL Stored Procedure

The most common delimiter between statements in SQL is by far the semicolon. However, since a Stored Procedure can contain several statements a new delimiter has to be defined for MySQL to understand where the statement end and where the Stored Procedure en while creating the procedure. Make note that you don't need to alter the delimiter when usign the procedure, but only while creating it. This is done as follows, and although any character can be used I chose to use "//"

DELIMITER //;

The actual create procedure I ended up usign is as follows

CREATE PROCEDURE links_recursive_cat (IN cat INT)
BEGIN
DECLARE a,c,i INT;
SET a = 1, c = 0, i = cat;
DROP TEMPORARY TABLE IF EXISTS lcat;
CREATE TEMPORARY TABLE lcat(id INT(3),name VARCHAR(30),num INT(3));
WHILE a = 1 DO
INSERT INTO lcat SELECT id,name,c FROM links_categories WHERE id = i;
SELECT sub INTO i FROM links_categories WHERE id = i;
IF c > 10 THEN
SET a = 0;
END IF;
IF i = 0 THEN
SET a = 0;
END IF;
SET c = c+1;
END WHILE;
INSERT INTO lcat VALUES(0,'Root',c);
END //

This procedure can be used by the following approach:

CALL links_recursive_cat(52)//
SELECT * FROM lcat ORDER BY num DESC//

Enjoy