CODEWITHSUNDEEP
sqlsql-server
Airo
Airo

Reputation: 68

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.

-- Query #1
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;

-- Query #2
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

Upvotes: 0

Views: 1018

Answers (2)

Yuri Gor
Yuri Gor

Reputation: 1463

Do a first select, check @@ROWCOUNT and if it's a zero do a second select

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

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

Upvotes: 1

Related Questions