Posted by Erland Sommarskog on 09/14/05 00:41

(yoohanman@yahoo.com) writes:
> I have a select statment that correctly returns zero rows at times. I
> would like to be able to return the value 0 (a single row with the
> value 0) whenever the logic returns zero rows.
>
> something like this
>
> If no.of.rows.returned = 0 then
> output 0
> else
> output query results
> end if
>
> Can anyone poing me in the right direction to do this?

A generic outline:

SELECT col1, col2, ...
INTO #temp
FROM ....

IF @@rowcount > 0
BEGIN
SELECT * FROM #temp ORDER BY ...
ELSE
SELECT 0

However, I am very much in doubt that this is a good idea. The client
code is likely to be confused if it expects a multi-column result set,
and suddently it gets a single-column result set.

In most situations, you can easily check client-side whether you got
any rows, and adapt your logic to that. But if that is for some reason
is difficult it might better to use an output parameter, or simply a
second result set that always return the row count.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]