MySQL stored procedure: check if an In parameter is null
I’m new to MySQL stored procedure (and MySQL in general), so I need some help because I can’t make my code work. I need to check if some input parameters are not null and run my stored procure according to them.
This is an example:
CREATE DEFINER=`root`@`localhost` PROCEDURE `game_procedure`(game_name varchar(30), year year(4), publisher varchar(30), price decimal) BEGIN if (game_name is null) and (price is null) then select distinct g.g_name, g.game_publisher, g.game_year, c.game_condition, c.game_price from game as g join catalogue as c on g.game_id=c.catalogue_game_id where g.game_year=year and g.game_publisher=publisher; end if; END
Of course, the other fields could be null too. What I want is to obtain different results depending on which In parameters I pass. I already tried with:
isnull(game_name), game_name is null
but nothing worked, expecially with numerical parameters (year and price – sintax error). Can you help me, please?
It isn’t clear what work you expect isnull(game_name), game_name is null
to be doing.
You’re getting syntax errors referring to year
because year
is a reserved word and you’re using it without wrapping it backticks (identifier quotes).