SQL IF SELECT query is null then do another query

Question

I have a query that regularly returns "nothing", and I would like to run a different query if this is the case, but I know not of the way of doing this. If anyone could be of help please.

Here is the current code I am using...

SELECT * FROM cfg_users JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId='0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' GROUP BY cfg_users.iUserId ORDER BY iStatusId, sName

I basically want to say

IF <my code> IS NULL THEN <do other code>, IF <my code> IS NOT NULL THEN return the result.

Thanks

The `if` statement is only allowed in stored programs. YOu might be able to set up what you want as a single query, but the other code is a mystery. — Gordon Linoff, Aug 04 '14 at 14:38

score 4 · Answer 1 · answered Aug 04 '14 at 15:42

There are some simple way only use sql.

Define your first query as a temp table, with union all, filter the second query with temp table's count.

with temp as (select * from t1 where 1=0)
select * from temp
union all
select * from t2 where (select count(*) from  temp) =0

This query will return the second table's records.

with temp as (select * from t1 )
select * from temp
union all
select * from t2 where (select count(*) from  temp) =0

And if temp query have result, only return temp query.

You can test with sql fiddle here.

John Ruddell · Accepted Answer · 2014-08-04T15:26:12.180

A way you can do it is like this

set two variables equal to the queries you want to execute.
set another variable equal to the correct query when the first is not null.
execute that query with a stored procedure.

STORED PROCEDURE:

DELIMITER $$

CREATE PROCEDURE `dynamic_query`(in input varchar(255))
BEGIN
    SET @a := input;
    PREPARE stmt FROM @a;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
$$

DELIMITER ;

THE TWO SELECTS YOU WANT TO EXECUTE:

SET @A := "SELECT * FROM  cfg_users JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId='0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' GROUP BY cfg_users.iUserId ORDER BY iStatusId, sName";
SET @B := "your other select here";

THE DEFINER TO GET THE CORRECT QUERY:

SET @C := (
SELECT
    CASE 
        WHEN EXISTS
            (   SELECT * 
                FROM  cfg_users 
                JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid 
                WHERE iTeamId='0' 
                    AND sDisabled IS NULL 
                    AND iStatusId > 0 
                    AND sDate = '2014-08-01' 
                GROUP BY cfg_users.iUserId 
                ORDER BY iStatusId, sName
            )
        THEN @A
        ELSE @B
    END
);

EXECUTE THE STATEMENT:

CALL dynamic_query(@C);

Wow, did not know this was a possibility. I shall have a go at this and see what happens. Thanks John Ruddell. — Greylegface, Aug 04 '14 at 15:32
@Greylegface you're welcome! Look at the demos to see it in action.. the trick is you need to set up the stored procedure and save it to the database once you do that this is a simple query :) — John Ruddell, Aug 04 '14 at 15:37

score 0 · Answer 3 · answered Aug 04 '14 at 15:39

You can store the results in a temporary table / table variable, and then check the count

e.g.

CREATE TABLE #Results ( --columns you need here )
INSERT INTO #Results SELECT * 
FROM  cfg_users 
JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId='0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' 
GROUP BY cfg_users.iUserId 
ORDER BY iStatusId, sName

SET @Count = SELECT COUNT(*) FROM #Results

IF 0 = @Count THEN
INSERT INTO #Results -- Other Query Here

SELECT * FROM #Results

n.b. you should really specify what columns you want in both queries rather than using *

SQL IF SELECT query is null then do another query

3 Answers3

Linked