mardi 1 août 2017

MySQL CONCAT truncates string inside IF THEN block

I've got an odd one here--at least odd to me.

I'm working on a fairly simple stored procedure. There are a number of parameters that can be passed into the procedure based on a web form. One of those is a sort order parameter. Records may be sorted by song_title, artist or date_uploaded, but I always want the records returned to be sorted by song_title first. This works just fine if the user chooses song_title or artist to sort by, but it seems to blow up if the user chooses 'date_uploaded'. I'm using a simple IF...THEN block to evaluate the sort order and modify it accordingly.

Here's the code:

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
    select concat('song_title,',orderBy);

    IF orderBy='date_uploaded' THEN SET orderBy='date_uploaded DESC';END IF;
    select orderBy;
    IF orderBy <> 'song_title' 
        THEN SET orderBy=CONCAT('song_title,',orderBy);
    END IF;
    select orderBy;

    SET @outputSQL=CONCAT('SELECT song_title, artist, disc_number, is_duet, comments
FROM track
WHERE song_title LIKE CONCAT(\'%',searchTitle,'%\')
AND artist LIKE CONCAT(\'%',searchArtist,'%\')
AND is_bad=0
AND is_duet=',duets,' 
ORDER BY ',orderBy ,' 
LIMIT ',startRecord,',', rowsReturned);


    PREPARE stmt FROM @outputSQL;
    EXECUTE stmt;   

END//
DELIMITER ;

So, what's happening is that in the block where I attempt to concatenate 'song_title' and whatever is in the orderBy parameter, it's truncating the value of that parameter. So date_uploaded DESC becomes date_uplo

Any thoughts on this? I'm fairly confident that the problem is within the IF THEN block as I've tested the concatenation code and it's producing the results that I want, but I'll be gobsmacked if I can see what's wrong with the syntax.

Aucun commentaire:

Enregistrer un commentaire