HOW TO: Check to see if there are any result sets

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)

HOW TO: Check to see if there are any result sets

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-05-18 : 09:23:52

Is there a function, variable or something to check to see if the current stored procedure has any results?

For instance:

IF @Case1 = 'expected value 1'
BEGIN
SELECT [field1] FROM [tablename]
END

IF @Case1 = 'expected value 2'
BEGIN
SELECT [field2] FROM [tablename]
END

IF @Case1 = 'expected value 3'
BEGIN
SELECT [field1], [field2] FROM [tablename]
END

IF @Case1 = 'expected value 4'
BEGIN
SELECT [field1], [field2], [field3] FROM [tablename]
END

IF @Case1 = 'expected value 5'
BEGIN
SELECT [field1], [field2] FROM [completelydifferenttable]
END

-- and so on...

Here, I want to know if there are any results, and if not, return a completely different result set. Is there a (better) way to do this?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-18 : 09:28:36

Save the value of @@ROWCOUNT variable after each select and check it afterwords.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 09:29:24

hismightiness?? now that's an egocentric nick

after each case check for @@rowcoount and put it in a variable.
if the variable is 0 in the end return your other resultset

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-05-18 : 09:30:11

Excellent! I thought of that right after I posted this, but I was hoping it wouldn't be the right answer. I have quite a few more checks than my post. :( I appreciate the help (and the QUICK reply).

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-18 : 09:30:49

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources