Stored Procedure Parameter working incorrectly

Question

I am running an SQL stored procedure with phpMyAdmin. I am using a Netflix 2023 database and running the following procedure.

SELECT Title,Genre,HoursViewed
FROM Netflix2023
WHERE Genre LIKE '%'+@MainGenre+'%'
ORDER BY HoursViewed DESC;

This does not output anything when I set MainGenre to be Drama.

However, when I run the following query

SELECT Title,Genre,HoursViewed
FROM Netflix2023
WHERE Genre LIKE '%Drama%'
ORDER BY HoursViewed DESC;

I do get rows output correctly. Can someone help with my stored query?

Answer 1

In MySQL, you should use the CONCAT function instead of the + for string concatenation

You should update the stored procedure to match the following example

DELIMITER //

CREATE PROCEDURE GetMoviesByGenre(IN MainGenre VARCHAR(255))
BEGIN
    SELECT Title, Genre, HoursViewed
    FROM Netflix2023
    WHERE Genre LIKE CONCAT('%', MainGenre, '%')
    ORDER BY HoursViewed DESC;
END //

DELIMITER ;