Abc254 Abc254 - 29 days ago 17
MySQL Question

Selecting all values from a table in mysql using a stored procedure

I have a table called Contacts with a field called person_id that I have connected to a java application.

If no value is specified for person_id in the application, I want to select everything from the contacts table using a stored procedure.

The operation I want to perform is this:

Select * from Contacts where (person_id like "%")


For this I have written a stored procedure shown below:

Delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectTest2`(In p_id int(11))
BEGIN
if p_id = null then
set p_id = "%";
end if;
select * from Contacts where (person_id like p_id);
END $$
Delimiter ;


However when I run this procedure in my sql using the following

call selectTest2(null)


The table that is returned is blank. How do I make it show all the values in the table?

The parameter p_id gets its value from a text box in the application. If the user has entered an id, I want the procedure to show only that particular record else I want it to show all records.

What have I done wrong and how do I correct it? I am aware that p_id is an int however I tried the same thing with other fields of type varchar and the table failed to return any value.

EDIT: viki888's solution has solved my problem however I am still want to know why the code posted above did not work. If somebody has the answer to this, please let me know.

Answer

Try using case statement in where clause like below

WHERE CASE WHEN p_id IS NOT NULL THEN person_id = p_id ELSE TRUE END

Hope this should solve your problem