MySQL Stored Prcedure Debugging Output

Question

So I've written a fairly simple MySQL stored procedure to retrieve values from a database for a personal app that I'm building. From everything I can see, the procedure should work just fine, but it's returning the wrong results.

Here's the procedure code:

USE randyrip_kdb;
DROP PROCEDURE IF EXISTS spGetAllTracksSong;
DELIMITER //

CREATE PROCEDURE spGetAllTracksSong(IN startRecord INT, IN rowsReturned INT, IN searchArtist VARCHAR(255), IN searchTitle VARCHAR(244), IN orderBy VARCHAR(20), IN duets TINYINT(1))
BEGIN
    DECLARE spStart INT;
    DECLARE spRows INT;
    DECLARE whereClause VARCHAR(255) DEFAULT '';
    DECLARE whereArtist VARCHAR(255) DEFAULT '';
    DECLARE whereSong VARCHAR(255) DEFAULT '';
    DECLARE outputSQL VARCHAR(1000) DEFAULT '';


    SET spStart=startRecord;
    SET spRows=rowsReturned;
    IF searchArtist!='' THEN SET whereArtist= CONCAT('artist LIKE \'%',searchArtist,'%\' '); END IF;

    IF searchTitle!='' THEN SET whereSong= CONCAT('song_title LIKE \'%',searchTitle,'%\' '); END IF;

    IF whereArtist != '' && whereSong !='' THEN SET whereClause=CONCAT('WHERE ', whereArtist,'AND ',whereSong);
    ELSEIF whereArtist !='' THEN SET whereClause= CONCAT('WHERE',whereArtist);
    ELSE SET whereClause = CONCAT('WHERE',whereSong);
    END IF;

    IF duets=1 && whereClause !='' THEN SET whereClause=CONCAT(whereClause,' AND is_duet=1');
    END IF;

    SET orderBy = IFNULL(orderBy, 'song_title');
    IF orderBy='date' THEN SET orderBy='date_added DESC'; END IF;
    /*select whereClause;
    select orderBy;
    select startRecord;
    select rowsReturned;*/
    SET outputSQL=CONCAT('SELECT song_title, artist, comments, disc_number FROM track ', whereClause,'ORDER BY ' ,orderBy,' LIMIT ' ,spStart,',',spRows);
    SELECT outputSQL;

    SELECT song_title, artist, comments, disc_number FROM track whereClause ORDER BY orderBy LIMIT spStart,spRows;

END//
DELIMITER ;

I'm calling the Stored Procedure with these parameters:

call spGetAllTracksSong(0,20,'elvis costello','peace, love','date',0);

The variable outputSQL is correctly generating the query I want, and when I run it it's returning two rows as expected. However, the procedure itself is returning 20 rows, none of which match the criteria.

If anyone has any ideas as to what I'm doing incorrectly, that would be great. From all that I can see, everything should be fine however.

slaakso · Accepted Answer · 2017-07-23 17:51:03Z

1

Randy, if you use variables in the SQL query (like "FROM track whereClause"), you need to execute with EXECUTE, otherwise it will not be evaluated. Replace your last select with this:

set @sql = outputSQL;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Alternatively, you could try not to use dynamic SQL.

answered Jul 23, 2017 at 17:51

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Randy Jackson Over a year ago

Okay, that worked. Can you further explain why this was necessary or point me towards some documentation explaining this? Also, why not use dynamic SQL?

slaakso Over a year ago

The "execute stmt" is required for MySQL to evaluate the string as SQL commands. If you do not use the "execute stmt", the "whereClause" is in your example, treated as a table alias (a shortcut name for table instance). With dynamic SQL, you should be careful about sql injection and on the other hand, by using direct SQL instead of dynamic SQL, your code will be more readable and easier to maintain. In your example, there really is no need for dynamic SQL.

Randy Jackson Over a year ago

Thanks a lot. That's all very helpful.

Collectives™ on Stack Overflow

MySQL Stored Prcedure Debugging Output

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related