Skip to content
Advertisement

Terse syntax to return an empty result set from SQL server

Is there a shorter way to write the following?


The “original” question follows. This was was modified may times, explicitly in hopes of stopping Y responses as a result of showing a specific use-case; and [rightly] claimed to be a confusing mess.

The use-case is a TSQL query that returns an empty result set in some cases while a ‘real’ result set in another. In both cases the structure is expected to be the same.

The question here is then, specifically about creating an empty result set / derived table simply.

One way to do this is as follows. Is there a syntactically ‘simpler’ method?


An alternative in this specific MINIMAL IF..ELSE.. CASE, it could be constructed as the following SQL. It is (XY) outside the question scope even though it would function here as the resulting schema is the same in the example above. While it may be a good option elsewhere, it requires a different TSQL flow-control structure. It will also not return the correct result sets if both cases result in a different schema – making it too specific in respect to the title scope.

Advertisement

Answer

Yes, there is a ‘simpler’ / shorter / more terse syntax to returning an empty result set in SQL Server that does not require first creating a derived table.

This is SQL Server specific syntax. There might be other similar forms found in other database implementations.


While not specifically about a shorter empty result set syntax, Ken White provided an approach which avoids duplicating schema if it’s identical in both cases. The IF flow-control structure is preserved, as per the original question.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement