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

Posted on 2008-11-18
Medium Priority
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
  • 2
  • 2

Expert Comment

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

Open in new window

LVL 143

Expert Comment

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

Author Comment

ID: 22988464
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22988714

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

Author Closing Comment

ID: 31517990
Works like a charm. Thank you!

Featured Post

Independent Software Vendors: 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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

621 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