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

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.
Who is Participating?
rafranciscoConnect With a Mentor Commented:
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.
Scott PletcherSenior DBACommented:
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.
sneidigAuthor Commented:
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.
sneidigAuthor Commented:
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.
sneidigAuthor Commented:
I'm going to use EXISTS. Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.