Assume that you have incoming parameter "ModerationStatus" that is responsible for filtering results.
Depending on this parameter you need to show either all rows or limited number of rows. Here's the example of the routine how this can be made with the least effort:

CREATE PROCEDURE `Photo_Get_List`(ModerationStatus tinyint(1))
BEGIN
SET @query = 'SELECT * FROM Photos';
IF ModerationStatus IS NOT NULL THEN
SET @query = CONCAT(@query, ' WHERE ModerationStatus = ', ModerationStatus);
END IF;
PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;

The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command:

One of those small thing that are not essential but yet very helpful - GROUP_CONCAT which returns grupped rows as one string with selected separator (comma by default). EG:

This will return something like:
1,2,4,12 | admin
3,5,6,7,8,9,10,11 | user

The result may be like this:
Ukraine; Russia; USA | admin
Russia; Canada; USA | user

This is the list of articles found by me or someone else regarding new features (mostly about stored procedures) in MySQL 5
MySQL 5.0 New Features: Stored Procedures and the link at the bottom of the page to PDF file containing extended article.

Stored Procedures and functions - the same in documentation on dev.mysql.com