使用存储过程从mysql中的表中选择所有值

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

我有一个名为Contacts的表，它与一个名为person_id的字段相连，这个字段是我连接到java应用程序的。

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

如果在应用程序中没有为person_id指定值，我希望使用存储过程从contacts表中选择所有内容。

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

但是，当我在sql中运行这个过程时，我使用以下命令

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.

参数p_id从应用程序的文本框中获取其值。如果用户输入了一个id，我希望过程只显示另一个特定的记录，我希望它显示所有的记录。

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.

我做错了什么，该怎么改正?我知道p_id是一个int类型的字段，但是我对varchar类型的其他字段进行了相同的尝试，并且表没有返回任何值。

1 个解决方案

#1

Try using case statement in where clause like below

尝试在where子句中使用case语句，如下所示

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

如果p_id不为空，那么person_id = p_id为真端

Hope this should solve your problem

希望这能解决你的问题

#1