Returning one set of results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Returning one set of results

Author

Topic

martind1
Starting Member

28 Posts

Posted - 2012-02-08 : 09:56:07

Hi,

I am running a query, and on the rare occurence it will return no results. If this happens I went it to run another query which will then return some results. So basically.


SELECT * FROM table WHERE Price > 0

If @@ROWCOUNT = 0
BEGIN
  SELECT * FROM table
END

But I only want it to return one set of results.
Any Ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-08 : 10:44:44

You can always use the brute force approach, as in:

IF EXISTS( SELECT * FROM table WHERE Price > 0 )
BEGIN 
	SELECT * FROM table WHERE Price > 0
END
ELSE 
BEGIN 
	SELECT * FROM table
END

martind1
Starting Member

28 Posts

Posted - 2012-02-08 : 11:13:07

I don't think that's really an option. as the query being executed has 15 joins lol.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-08 : 11:29:47

This?

SELECT * into #MyTmpResult FROM table WHERE Price > 0

If @@ROWCOUNT = 0
BEGIN
SELECT * FROM table
END

ELSE
Begin
select * from MyTmpResult
End

No, you're never too old to Yak'n'Roll if you're too young to die.

martind1
Starting Member

28 Posts

Posted - 2012-02-08 : 11:31:45

Ahh, that's a good idea! (simple one too) Think I just needed a fresh brain to work it out lol
Cheers :)

--
http://www.tutorial-resource.com - Free Web Development Resources

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources