CODEWITHSUNDEEP
phpmysqlsqlstored-procedures
Donnie Rock
Donnie Rock

Reputation: 525

stored procedure in mysql to make a select with variable params

I'm having a problem with a stored procedure in MySQL. I need to call it from a PHP code (I can't use the select straight from the code). The procedure receive the parameters from a form, and that parameters could be a string or numeric value (if the user fills the input) o a null (if the user left the input blank).

I made this code, but it doesn't work (procedure and variable names are in Spanish) and it didn't show any error.

DELIMITER $$

CREATE PROCEDURE ps_BusquedaVarParam
    (IN nombre varchar(30), apellidos varchar(50), minEdad int, maxEdad int)
BEGIN
  SELECT * FROM alumnos WHERE 
(nombre is null or Nombre = nombre) and 
(apellidos is null or Apellido = apellidos) and 
(minEdad is null or Edad>=minEdad) and 
(maxEdad is null or Edad<=maxEdad);
END $$

DELIMITER ;

Upvotes: 0

Views: 146

Answers (1)

jaczes
jaczes

Reputation: 1394

Try that:

DELIMITER $$

CREATE PROCEDURE ps_BusquedaVarParam(nombre_in varchar(30), apellidos_in varchar(50), minEdad_in int, maxEdad_in int)
BEGIN
 SELECT * FROM alumnos WHERE 
(nombre_in is null or Nombre = nombre_in) 
and (apellidos_in is null or Apellido = apellidos_in) 
and (minEdad_in is null or Edad>=minEdad_in) 
and (maxEdad_in is null or Edad<=maxEdad_in);
END $$

DELIMITER ;

Upvotes: 2

Related Questions