sneidig
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
ASKER
I'm going to use EXISTS. Thank you.
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.