Watch this week's video on YouTube
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:
```sql
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:
```sql
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:
```sql
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:
```sql
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.
```sql
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:
```sql
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.