Is there a shorter way to write the following?
-- select empty_result select t.col from (select 1 as col) t where 1 = 0 -- never match
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.
if @foo = 'bar' -- select real_result else -- select empty_result
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?
-- select empty_result select t.col from (select 1 as col) t where 1 = 0 -- never match
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.
-- XY alternative specific to MINIMAL CASE shown above -- select real_result or empty_result with single query select t.col from real_data t where @foo = 'bar'
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.
-- select empty_result select top 0 1 as col
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.
if @foo = 'bar' -- select real_result select t.col from real_table t else -- select empty_result select t.col from real_table t where 1 = 0