Check in Stored Procedure If no rows are returned, then run a different query.

Posted on 2005-04-12
Medium Priority
Last Modified: 2007-12-19
Hello, Thank you for reading.

I'm building some search functionality that makes use of stored procedures (sprocs) in SQL Server 2000. I have an idea to set the default search to be an "And" search. Then I would like to check, in the sproc, to see if no rows are returned from the "And"  search. If there are zero rows returned, then I would like to run the exact same query, but with "Or" logic instead of "And" logic and return those results instead.

I'm not sure if this is possible to do in SQL Server, but I was wondering if someone could tell me if it's possible and maybe provide some code or point me in the right direction. I'm not sure exactly how to search for code examples related to this question.

Let me know what you think, thanks for your time.
Question by:sneidig
  • 3
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13766151
It's easy to tell if the first query returned rows or not -- just check @@ROWCOUNT.

The "trick" is if you want to return only one result set to the caller.  That's probably not worth the trouble.  It would be better if at all possible to have the app code check the first result set and if empty use the second one.
LVL 28

Accepted Solution

rafrancisco earned 1000 total points
ID: 13766190
You can do it this way:

IF EXISTS (SELECT 'X' FROM YourTable WHERE Field1 = @Parameter1 AND Field2 = @Parameter2)
    SELECT * FROM YourTable WHERE Field1 = @Parameter1 AND Field2 = @Parameter2
    SELECT * FROM YourTable WHERE Field1 = @Parameter1 OR Field2 = @Parameter2

This way, your application will receive only 1 result set.

Author Comment

ID: 13766211
Thanks Scott, I'm sure my old SQL Server professor would be rolling his eyes that I had to ask a question about @@ROWCOUNT. I just don't use the programming language in SQL Server often enough to remember anything (apparently) about it.

I think if only one row in the table meets all the AND criteria, then that's the row we want to return and it should be the correct row the user was searching for.

But if no rows are returned, then perhaps it's because the user misspelled one of the search criteria fields and it would be a shame if the search returned zero rows because of that. So then I figure, why not give the user the OR results and maybe they can find what they're looking for.

Author Comment

ID: 13766242
rafrancisco I see now that I'll have to run the AND query at least once just to figure out if it returns rows, then again if it does, and the OR query if it doesn't.

Author Comment

ID: 13766446
I'm going to use EXISTS. Thank you.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question