CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
user240141
user240141

Reputation:

Is it possible to return empty row from Sql Server?

Is this possible to return all columns in query as empty column (not null) or empty row, in case the actual query is returning no rows

Upvotes: 10

Views: 35224

Answers (4)

YouAskSomeYouAnswerSome
YouAskSomeYouAnswerSome

Reputation: 38

I can't comment due to Karma, so here is a more full answer addressing some of the other users' comments:

Accepted Answer

The accepted answer is of this form:

WITH data AS (
  ...your query...
), dummy AS (
  SELECT 1 AS dummy
) SELECT data.*
  FROM dummy
  LEFT JOIN data

This works because it joins your query's output rows against a single row then disregards the columns of that row. It is a LEFT JOIN, so when a contains rows and b does not, all rows of a will return with the b columns set to NULL.

This is great! Except this runs a JOIN the length of b which could be expensive.

Better answer

There is a better answer that doesn't rely on column names and will not scan the table, which as noted in a comment will give an extra row.

This following query builds on that and uses a WHERE NOT EXISTS to eliminate the extra row when it isn't needed.

WITH data AS (
  ...your query...
), nulls AS (
  SELECT
    null AS a,
    ...for each column output by your query...
) SELECT * FROM data
  UNION ALL
  SELECT * FROM nulls WHERE NOT EXISTS (SELECT * FROM data)

The major downside here is that you have to write out all of your columns as NULL, which will be hard in some situations.

Best answer

WITH data AS (
  ...your query...
), single_row AS (
  SELECT * FROM data LIMIT 1
), nulls AS (
  SELECT single_row.*
  FROM (SELECT 1 AS dummy) dummy
  LEFT JOIN single_row
  WHERE NOT EXISTS (SELECT * FROM single_row)
) SELECT * FROM data
  UNION ALL
  SELECT * FROM nulls

nulls will contain a row only when data does not. nulls gets its columns from a virtual row of data.

Upvotes: 0

Fandango68
Fandango68

Reputation: 4868

Based on Richard's answer, you can use UNIONS to give you "something"...

select t.a, t.b, t.c
from (select null AS a, null AS b, null AS c
       union ALL
      select a, b, c from tbl)   -- original query
     ) AS t 
on 1=1

The '1=1' is really what forces SQL to return something.

Upvotes: 0

Philippe Grondier
Philippe Grondier

Reputation: 11138

If your objective is to return a query with no records, or with an empty recordset/dataset, the following should work without any previous knowledge on the original query:

SELECT * FROM (myOriginalQuery) as mySelect WHERE 0 = 1

Upvotes: 6

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Generally, if you must have an empty row returned..

If your original query is

select a,b,c from tbl

You can turn it into a subquery

select t.a,t.b,t.c
from (select 1 as adummy) a
left join (
    select a,b,c from tbl  -- original query
) t on 1=1

Which ensures the query will always have a rowcount of at least one.

Upvotes: 20

Related Questions