user3772999 user3772999 - 7 months ago 10
SQL Question

Perform second part of query only if first part returns nothing

Here is my Query.

select * from jobs where status='BUILDING' and Type='SLA' UNION ALL
select top 1 * from jobs where Scheduled_Time = (select min(Scheduled_Time) from Jobs
where status='IDLE' and type='SLA')


I am new to MSSQL and was wondering how can I get this query to work so that if the first query (right before UNION ALL) does not return null, then the second query will not run? Likewise, the second query should run only if the first query returns null. (Obviously, UNION ALL is not the answer as this includes both results, I have just used this as a filler and this will be removed).

Answer
IF EXISTS (select * from jobs where status='BUILDING' and Type='SLA')
BEGIN
select * from jobs where status='BUILDING' and Type='SLA' UNION ALL 
select top 1 * from jobs where Scheduled_Time = (select min(Scheduled_Time) from Jobs 
where status='IDLE' and type='SLA')
END

Maybe this query has performance issue but this works just like you said. To avoid from bad performance you can change query to this :

DECLARE @result1 TABLE (a INT, Type varchar, status varchar)

INSERT INTO @result1
select * from jobs where status='BUILDING' and Type='SLA'

 IF EXISTS (select * from @result1)
    BEGIN
    select * from @result1 UNION ALL 
    select top 1 * from jobs where Scheduled_Time = (select min(Scheduled_Time) from Jobs 
    where status='IDLE' and type='SLA')
    END

UPDATE

This is the correct query that you said in comments :

IF EXISTS (select * from jobs where status='BUILDING' and Type='SLA')
 BEGIN
 select * from jobs where status='BUILDING' and Type='SLA' 
END 
ELSE
 select top 1 * from jobs where Scheduled_Time = (select min(Scheduled_Time) from Jobs where status='IDLE' and type='SLA')
Comments