How to exclude a @variable in SQL query if a control does not contain a value

Posted on 2008-11-18
Last Modified: 2012-05-05
I have a search page with three dropdownlist controls and a search button. A simple select query to retrieve data in a datalist control is on the same web page. I like to filter data based on the three dropdownlist controls. In the filter column of my query I put a variable like this @controlname. One of the dropdownlist controls does not require input. If I put a variable for the field in the query I get an error message "...expects the parameter @controlname, which was not supplied". I have tried isnull(@controlname) did not work. How can I execute the query if no input in this particular control then show alle records based on the other control variables?
Question by:PdeLorme
    LVL 6

    Expert Comment

    Change the select criteria to
    LIKE isnull(@controlname,'%')

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    please show the relevant code.

    Author Comment

    This is the part of the where clause in my SQL statement. The SQL statement will be used as objectdatasource in my datalist control.

    WHERE     (GuestID = @AantalGuests) AND (RoomID = @AantalRooms) AND (City = @CityAirport)

    The variable @AantalGuests must be optional. If no value is selected in the dropdownlist, it should not be part of the filter on the table content. Btw, LIKE isnull(@controlname,'%') gives an error "data type error in expression". Hope you find a solution!
    LVL 142

    Accepted Solution


    WHERE     (GuestID = @AantalGuests OR  @AantalGuests  IS NULL) AND (RoomID = @AantalRooms) AND (City = @CityAirport)

    Author Closing Comment

    Works like a charm. Thank you!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    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 shrink a transaction log file down to a reasonable size.

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now