How to select an empty result set?

sql mysql stored-procedures null resultset
Petruza picture Petruza · Sep 21, 2009 · Viewed 45.8k times · Source

I'm using a stored procedure in MySQL, with a CASE statement.

In the ELSE clause of the CASE ( equivalent to default: ) I want to select and return an empty result set, thus avoiding to throw an SQL error by not handling the ELSE case, and instead return an empty result set as if a regular query would have returned no rows.

So far I've managed to do so using something like:
Select NULL From users Where False

But I have to name an existing table, like 'users' in this example. It works, but I would prefer a way that doesn't break if eventually the table name used is renamed or dropped.

I've tried Select NULL Where False but it doesn't work.

Using Select NULL does not return an empty set, but one row with a column named NULL and with a NULL value.

Answer

Björn picture Björn · Sep 21, 2009

There's a dummy-table in MySQL called 'dual', which you should be able to use.

select
    1
from
    dual
where
    false

This will always give you an empty result.

Related questions

MySQL: Selecting multiple fields into multiple variables in a stored procedure

Can I SELECT multiple columns into multiple variables within the same select query in MySQL? For example: DECLARE iId INT(20); DECLARE dCreate DATETIME; SELECT Id INTO iId, dateCreated INTO dCreate FROM products WHERE pName=iName; What is the correct syntax …

Read More
Mysql - How to quit/exit from stored procedure

I have very simple question but i did't get any simple code to exit from SP using Mysql. Can anyone share with me how to do that? CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20)) BEGIN IF tablename IS NULL THEN #…

Read More
Is it possible to have a default parameter for a mysql stored procedure?

I have googled this and keep coming up with "No it is not possible" but these posts were dated 2005-2007 so I'm wondering if this has been changed. A code example: CREATE PROCEDURE `blah` ( myDefaultParam int = 0 -- This breaks the …

Read More