While I normally prefer formatting my query results in a downstream app/reporting layer, sometimes I can’t get around adding some business formatting logic to my SQL queries.

Today I want to show you four different ways to conditionally output rows of data based on a SQL query.

Setting Up The Base Query

You’ve probably worked with a query that results in either 0 or 1 rows being returned:

DECLARE @CurrentDatetime datetime = getdate();
?SELECT?
    1 AS AreSecondsDivisbleBy2,
?    @CurrentDatetime AS CurrentDatetime?
WHERE?
    DATEPART(second,@CurrentDatetime) % 2 = 0

If you put that query into a derived table and add some IIF() logic on top of it, you now have a situation where you your result may contain a row with one of two distinct values or no rows at all:

If you put that query into a derived table and add some IIF() logic on top of it, you now have a situation where you your result may contain a row with one of two distinct values or no rows at all:

DECLARE @CurrentDatetime datetime = GETDATE();
??SELECT?
    IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisbleBy3And2,
?    d.CurrentDatetime?
FROM
    (?
    SELECT?
        1 AS AreSecondsDivisbleBy2,
?        @CurrentDatetime AS CurrentDatetime
?    WHERE
?        DATEPART(second,@CurrentDatetime) % 2 = 0?
    ) d

(Note: if you ever need to know whether the seconds part of the current time is divisible by 3 and 2, use SELECT IIF(DATEPART(second,GETDATE()) % 6 = 0,1,0) and not this monstrosity of a query  I’m creating to demonstrate a scenario when you dependent derived table logic).

Sometimes we may want to force certain scenarios based on the output of the query above. Here are few common patterns that I find myself doing when needing to meet certain business requirements of queries to return data conditionally.

Always Return 1 Row

Let’s say we are happy with getting a result value of 0 or 1 for AreSecondDivisbleBy3And2, but want to additionally return some other value when our derived table returns no rows. We can accomplish this with a UNION ALL and some sorting:

DECLARE @CurrentDatetime datetime = GETDATE();??
SELECT TOP 1
?    AreSecondsDivisbleBy3And2,
?    CurrentDatetime?
FROM?
    (?
    SELECT
        IIF(DATEPART(second,CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisbleBy3And2,
?        d.CurrentDatetime,
?        0 AS OrderPrecedence
?    FROM?
        (
?        SELECT
?            1 AS AreSecondsDivisbleBy2,?
            @CurrentDatetime AS CurrentDatetime?
        WHERE?
            DATEPART(second,@CurrentDatetime) % 2 = 0?
    ) d
?    UNION ALL
?    SELECT?
        -1 AS AreSecondsDivisbleBy3And2,
?        @CurrentDatetime AS CurrentDatetime,
?        1 AS OrderPrecedence
?    ) p
ORDER BY
 ?    OrderPrecedence

We can limit our query to return TOP 1 and then add an OrderPrecedence column to determine which query result row to return. If our original query has a row of data, it will be returned because of it’s OrderPrecedence. If our original query returns 0 rows, our fall back UNION ALL default value of -1 will be returned.

Return 1 row when value is 1, 0 rows when value is 0

What about a situation where we want to return a row when AreSecondsDivisbleBy3And2 is equal to 1, but no row when it is equal to 0?

The IIF function and CASE statements work great for conditionally returning a value in a SELECT, but how can we conditionally return a row?  Here’s one approach:

DECLARE @CurrentDatetime datetime = GETDATE();
??SELECT
 ?    AreSecondsDivisibleBy3And2,
?    CurrentDatetime?
FROM?
    (?
    SELECT
?        IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2,
?        CurrentDatetime?
    FROM?
        (?
        SELECT
?            1 AS AreSecondsDivisbleBy2,?
            @CurrentDatetime AS CurrentDatetime
?        WHERE?
            DATEPART(second,@CurrentDatetime) % 2 = 0
?        ) d?
    ) d1
?    INNER JOIN
    (?
    SELECT?
        0 AS ValueToNotReturn
?    ) d2?
        ON d1.AreSecondsDivisibleBy3And2 != d2.ValueToNotReturn

In this scenario, we return a row when our value is 1, but do not return a row when the value is 0 or our derived table doesn’t return any rows.

We use an INNER JOIN to filter out the row value that we want to return 0 rows.

Return 1 row when null, and 0 rows when the value is 1 or 0

In this scenario we want to return no rows when AreSecondsDivisibleBy3And2=1 and a row when its not.

I’ve never had a real-world use for this one, but it’s essentially a combination of the first two solutions.

SELECT?
    AreSecondsDivisibleBy3And2,?
    CurrentDatetime
FROM?
    (
    SELECT TOP 1
        AreSecondsDivisibleBy3And2,?
        CurrentDatetime
    FROM
        (
        SELECT
            IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2,
?            d.CurrentDateTime,
?            0 AS OrderPrecedence?
        FROM
            (
            SELECT
                1 AS AreSecondsDivisbleBy2,?
                @CurrentDatetime AS CurrentDatetime?
            WHERE
                DATEPART(second,@CurrentDatetime) % 2 = 0?
            ) d?
        UNION ALL?
        SELECT
 ?           -1 AS AreSecondsDivisibleBy3And2,?
            @CurrentDatetime AS CurrentDatetime,?
            1 AS OrderPrecedence
?        )u?
        ORDER BY
 ?            OrderPrecedence
    ) d1
    INNER JOIN
 ?   (
?    SELECT?
        -1 AS ValueToNotReturn
?    ) d2
?        ON d1.AreSecondsDivisibleBy3And2 = d2.ValueToNotReturn

This one is by far the most difficult one to logic through, but it’s a pure reversal of return no rows when rows are present and return a row when no rows are present.

Always return 0 rows

I’m not exactly sure what the business case for never returning any rows would be, but this one is pretty simple: just add a condition that will always evaluate to false:

DECLARE @CurrentDatetime datetime = GETDATE();??
SELECT?
    IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2
?FROM?
    (?
    SELECT
        1 AS AreSecondsDivisbleBy2,?
        @CurrentDatetime AS CurrentDatetime
?    WHERE
        DATEPART(second,@CurrentDatetime) % 2 = 0
?    ) d?
WHERE
    1=0

Since 1=0 will never be true, your query will never return any results regardless of what kind of logic is happening in your SELECT or derived table.