Procedure stored in mysql

Question

Procedure stored in mysql

Navigation

#1 by (0 votes)

0

Hi guys, good morning again here with new questions.

I'm doing a maintainer in java Desktop and in the search part I have a text field where I would enter the value to search, but I also have a radiobuttons that would allow me to choose the field of my table to search.

Let me explain: I have a table of suppliers with 3 fields: name, country, region. Then in my application I have a text box where I enter the value to search, but also the radiobuttons allow me to choose between searching in the field name or in the country field or in the field region.

Then my SP in Mysql I did it like this:

DELIMITER $$
CREATE PROCEDURE listarProveedores(campo varchar(50), valor varchar(50))
BEGIN
  select * from suppliers where campo like concat('%',valor,'%');
END
$$
DELIMITER ;

And when I call the SP:

CALL listarProveedores('ContactName','ch');

He does not show me anything.

Please have someone give me an option.

Thanks in advance.

mysql java

asked by Carlos Eduardo Rodríguez 11.01.2018 в 19:43

source

1 answer

How can I change values of a select using JQuery? Delete repeated data oracle

score 0 · Answer 1

The problem is that the procedure variables can not be included in the SQL as such. The solution to your problem is a prepared statement. You could do something like this:

DELIMITER $$
CREATE PROCEDURE listarProveedores(campo varchar(50), valor varchar(50))
BEGIN
    SET @buffer = CONCAT("SELECT * FROM suppliers WHERE ", campo, " LIKE %", valor, "%'");
    PREPARE stmt FROM @buffer;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;

Or you could do it through a CASE:

DELIMITER $$
CREATE PROCEDURE listarProveedores(campo varchar(50), valor varchar(50))
BEGIN
  CASE campo
     WHEN 'a' THEN SELECT * FROM suplliers WHERE a LIKE CONCAT('%',valor,'%');
     WHEN 'b' THEN SELECT * FROM suplliers WHERE b LIKE CONCAT('%',valor,'%');
     WHEN 'c' THEN SELECT * FROM suplliers WHERE c LIKE CONCAT('%',valor,'%');
  END CASE;
 END
 $$
 DELIMITER ;