Anoml Anoml - 2 years ago 152
SQL Question

Run second query if first query does not return any results and return

I am trying to keep this to the minimal table queries to ensure less database usage. I am using Microsoft SQL Server Management Server.

I have a query which will sometimes return nothing depending on the user's current status. If this first query does not return any results, I would like the second query to run.

This is inside of a function and requires returning a single row of column data. I will include the queries with names changed for an example. I will do my own optimization after to make a temp table so the database is not accessed as often. I just need to figure out how to make this work first.

--QEURY1
INSERT @tlbReturn (returnInfo1, returnInfo2)
SELECT TOP(1) returnInfo1, returnInfo2
FROM table1 AS t1a
INNER JOIN table1 AS t1b ON t1a.someData1 = t1b.someData1
AND t1a.someData2 = t1b.someData2
AND t1a.someData3 = t1b.someData3
AND t1a.someData4 = t1b.someData4
INNER JOIN table2 AS t2 ON t2.someData6 = t1b.someData7
AND t2.someData8 = t1b.someData9
WHERE t1a.someData10 = 'value'
AND t1b.someData11 IN ('value1', 'value2')
ORDER BY t1b.someDate DESC;

--QUERY2
INSERT @tlbReturn (returnInfo1, returnInfo2)
SELECT TOP(1) returnInfo1, returnInfo2
FROM table1 AS t1a
INNER JOIN table1 AS t1b ON t1a.someData1 = t1b.someData1
AND t1a.someData5 = t1b.someData5
INNER JOIN table2 AS t2 ON t2.someData6 = t1b.someData7
AND t2.someData8 = t1b.someData9
WHERE t1a.someData10 = 'value'
AND t1b.someData11 IN ('value1', 'value2')
ORDER BY t1b.someDate DESC;


In theory I would like something like,

IF EXISTS(QUERY1) THEN RETURN
ELSE RETURN QUERY2

Answer Source

Check the value of @@ROWCOUNT after the first query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download