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

Posted on 2005-04-12
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
    LVL 68

    Expert Comment

    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

    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

    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

    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

    I'm going to use EXISTS. Thank you.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now