CODEWITHSUNDEEP
sqlsql-server
user3772999
user3772999

Reputation: 59

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).

Upvotes: 3

Views: 1000

Answers (2)

Saman Gholami
Saman Gholami

Reputation: 3512

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')

Upvotes: 3

user3772999
user3772999

Reputation: 59

I appreciate the help Saman. Your IF EXISTS helped me out to begin with. This query fixed the issue for me. Just had to add a simple else statement. 

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')

Upvotes: 0

Related Questions