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