Clear the result set in a MySQL procedure

Question

I want to define a MySQL stored procedure, which returns data in the result set. Sometimes, I want to return nothing (ie, zero rows).

How can I do this? SELECT; is invalid, and SELECT NULL; returns a record with a NULL value. How to select an empty set?

You need a `FROM` clause that references a valid row source, and a predicate that prevents any rows from being returned. @MarcB: I believe you need a FROM clause in order to use the `WHERE` clause. — spencer7593, Apr 24 '15 at 17:52
@spencer7593: yep. just tested it out. so... `from dual` works nicely. — Marc B, Apr 24 '15 at 17:53

score 2 · Accepted Answer · answered Apr 24 '15 at 17:53

2

This does the trick:

mysql> select null from dual where 1=0;
Empty set (0.00 sec)

answered Apr 24 '15 at 17:53

Marc B

Thanks! I did not know the DUAL keyword existed. – galinette Apr 24 '15 at 20:39
What is `dual` and where does it come from? – Dragas Aug 29 '18 at 10:56

score 0 · Answer 2 · answered Mar 06 '19 at 19:58

0

The selected answer will work, but I prefer this approach:

SELECT NULL LIMIT 0

It should also work on for other variants of SQL besides just MySQL.

answered Mar 06 '19 at 19:58

sam-6174

2 Answers2