By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,900 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,900 IT Pros & Developers. It's quick & easy.

How to override zero rows returned

P: n/a
Hi,

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?

many thanks,
yohan

Sep 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
USE NORTWIND
GO

IF EXISTS (SELECT * from Orders where orderID = 10235)
SELECT * from Orders where orderID = 10235
ELSE
SELECT 0
HTH, Jens Suessmeyer.

Sep 13 '05 #2

P: n/a
(yo*******@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, es****@sommarskog.se

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

Sep 13 '05 #3

P: n/a
Why do you want to have Unpredictable results from a query? A scalar
is not a table. This is fundamentals!!

Sep 14 '05 #4

P: n/a
True. I dont want to mix and match. But the software that uses this
query considers it an error if no rows are returned, and cannot be
overridden.

Thanks everyone for the ideas, i'll give it a shot.

Sep 14 '05 #5

P: n/a
True. I dont want to mix and match. But the software that uses this
query considers it an error if no rows are returned, and cannot be
overridden.

Thanks everyone for the ideas, i'll give it a shot.

Sep 14 '05 #6

P: n/a
True. I dont want to mix and match. But the software that uses this
query considers it an error if no rows are returned, and cannot be
overridden.

Thanks everyone for the ideas, i'll give it a shot.

Sep 14 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.