SP Results Help - 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 2005 Forums

Transact-SQL (2005)

SP Results Help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2010-09-10 : 16:47:01

I have a sp that contains 2 queries, what I want to do is if the first result returns nothing, then run the second querie else just return results of first query.

I have no idea how to go about that - any suggestions or help appreciated.

Thanks

CSears
Starting Member

39 Posts

Posted - 2010-09-10 : 16:50:05

[code]
IF EXISTS(QUERY1)
QUERY1
ELSE
QUERY2
[/code]

ann
Posting Yak Master

220 Posts

Posted - 2010-09-10 : 16:54:17

Thank you for the quick reply - I'll try this out

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-10 : 16:54:48

or...


SET NOCOUNT ON

Query1
IF @@RowCount = 0
Query2

ann
Posting Yak Master

220 Posts

Posted - 2010-09-10 : 16:56:05

That worked out great - thanks so much! :)

CSears
Starting Member

39 Posts

Posted - 2010-09-10 : 17:15:26

quote:
Originally posted by russell

or...

SET NOCOUNT ON

Query1
IF @@RowCount = 0
Query2

The only problem with this solution is that you end up with two tables just the first one is empty. Depending on how you are using this SP this could cause problems. Although I will admit using @@ROWCOUNT is by far the more fun way to code :D

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-10 : 17:27:33

I don't understand what you're saying, but there is nothing wrong with my solution. If the 1st query returns a result, it is returned to the client. If it doesn't, it executes the 2nd query.

You don't end up with any tables -- you end up with a single result set. The SET NOCOUNT ON at the top prevents OLEDB clients from choking on the 2nd select statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 17:54:50

russell,

Yours actually ends up with two result sets in the case where the first has 0 rows. The first one will just be empty, but the columns are still returned.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-13 : 15:13:25

ok, don't know what i was thinking about. it returns 1 if the 1st query returns any records, 2 if it doesn't.

obviously. thats why i put the set nocount on. sorry CSears.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources