How can I force the query to return values?

I want the following query to return a row even when table 'X' is empty. How would I do this?

SELECT TOP 1 @.Var1, @.Var2, @.Var3 from X

The parameters @.Var1, @.Var2 and @.Var3 are passed to the stored procedure in which the above query is included.

When table is empty, it reurn nothing. It only return a row when table is not empty.

The following should work:

SELECT TOP 1 Coalesce(@.Var1, "-1"), Coalesce(@.Var2, "-1"), Coalesce(@.Var3, "-1") from X

Coalesce will evaluate if the value of the field is null, if so it will return whatever value you specify in the second half of the Coalesce statement (I put "-1" in there as an example.)

-Aaron

|||

It doesn't work. I think you assumed that @.Var1 is a column. It's a sp parameter.

I ran your query for an empty table and it returned no rows.

|||

Ahhh yes, I did assume it was a column.

Could you post your full query code so I can get a better understanding of exactly what you are doing?

|||

You could do something like this:

IFEXISTS (SELECT *FROM ..WHERE ..)BEGINSELECT..FROM ...WHERE ...ENDELSEBEGINSELECT..FROM..WHERE 1=0END

|||

The situation is pretty straight forward and in one minute you will be be ready-to-go:Create a table X with no rows. Then run the following query on it:

select top 1 @.var1, @.var2, @.var3 from X

You will notice that till table X has rows, the above query will not return anything.

The challenge here is to return a row even when table X is empty.

|||

If what I posted doesnt work you can use SELECT NULL, NULL in the NOT EXISTS part of the IF condition.

|||

This may sound stupid... but working

IF(SELECTCOUNT(*)FROM EMPTY_TABLE)=0

BEGIN

Select'1'as Col1,'2'as Col2,'3'as Col3

END

ELSE

BEGIN

SelectTOP 1'1'as Col1,'2'as Col2,'3'as Col3from EMPTY_TABLE

END

? if this is what you want replace the constants with parameter

|||

Not sure why you want this (would be interested to know) but here you go:

select top 1 t1.var1, t1.var2, t1.var3from (select @.Var1, @.Var2, @.Var3)as t1 ( var1, var2, var3)leftouter join Xon 1=1-- example heredeclare @.xintset @.x = 1select top 1 t1.x, o.*from (select @.x) t1 (x)leftouter join (select *from sys.objectswhere 1=0) oon 1=1

Use an inventive LEFT OUTER JOIN:|||

As a side note, anyone needing to check whether rows exist in a table or not such as here

satya_tanwar:

IF(SELECTCOUNT(*)FROM EMPTY_TABLE)=0

Would beMUCH better served usingEXISTS:

IFEXISTS (select 1 from tbl)BEGIN-- code for data that existEND ELSE BEGIN-- code for data that doesn't existEND

The reason is that using COUNT(*) means the table or an index needs to be iterated. Using EXISTS checks for the first record to be returned and then that processing stops with success.|||

Quick addendum to David's suggestion.

It doesnt matter if you use 1 or COUNT(*) or <column1> if you use EXISTS.

IF EXISTS( SELECT COUNT(*) FROM Table WHERE <condition>. EXISTS returns a boolean so as soon as it finds the first record that matches the condition, it will return with a TRUE. The subquery doesnt return any result set when used with an EXISTS.

|||

ndinakar:

It doesnt matter if you use 1 or COUNT(*) or <column1> if you use EXISTS.

Actually...it matters quite a bit. SELECT COUNT(*) FROM tbl will always return a singleton (not including a GROUP BY clause in the query) butSELECT <something> FROM tbl may not. So:

ndinakar:

IF EXISTS( SELECT COUNT(*) FROM Table WHERE {condition}. EXISTS returns a boolean ...

Comparing that IF statement to this one

IFEXISTS (select 1from tblwhere {condition})

There is quite a difference in how these statements behave.

|||

Here's something from BOL: Am I understanding this wrong?

--------------

When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test.The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

Notice that subqueries that are introduced with EXISTS are a bit different from other subqueries in the following ways:

The keyword EXISTS is not preceded by a column name, constant, or other expression.
The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.
|||

Where the confusion lies is that the querySELECT COUNT(*) FROM TBL does in fact return a record even when no data exists in the table. Here is an example to demonstrate

declare @.ttable (idint)ifexists (selectcount(*)from @.t )print'Data Exists! [using count(*)]'else print'No data! [using count(*)]'ifexists (select 1from @.t )print'Data Exists!'else print'No data!'

If both methods were equivalent, then you would seeNo Data! both times. Since the COUNT(*) query returns a singleton, it in fact causes a TRUE to be returned from the EXISTS() function.

|||

Oh ok. I see your point. And its a good point. Even if there are no rows matching the condition, the query would return a 0 which translates to TRUE.