Filter search query

I have a table with the columns City and Z开发者_StackOverflow社区oneCode. I have a dropdown list where I can select just one City or all. And another dropdownlist for the ZoneCode. Basically they are filters that only filter if I select a value from the dropdown lists.

Is there any recommended technique to do this?

edit: this table is only an example.

Thank you

---

Since you've tagged this as sql-server, I'm going to assume that you're looking for recommendations on the database side, as opposed to how to do it in the front-end.

My recommendation is: Normalize it. If a ZoneCode belongs to a particular City, then you should have a table of Cities and a Table of ZoneCodes, with your Customer table (or whichever table has these columns now) referencing a ZoneCodeID.

Example schema:

CREATE TABLE Cities
(
    CityID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Cities PRIMARY KEY CLUSTERED,
    CityName varchar(100) NOT NULL
)

CREATE INDEX IX_Cities_Name
ON Cities (CityName)

CREATE TABLE ZoneCodes
(
    ZoneCodeID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_ZoneCodes PRIMARY KEY CLUSTERED,
    CityID int NOT NULL
        CONSTRAINT FK_ZoneCodes_Cities FOREIGN KEY
            REFERENCES Cities (CityID)
                ON UPDATE NO ACTION,
                ON DELETE NO ACTION
    ZoneCode varchar(10) NOT NULL
)

CREATE INDEX IX_ZoneCodes_City
ON ZoneCodes (CityID)
INCLUDE (ZoneCode)

To filter it's as simple as:

SELECT ZoneCodeID, ZoneCode
FROM ZoneCodes
WHERE (@CityID IS NULL) OR (CityID = @CityID)

Or, if you only have the name:

SELECT z.ZoneCodeID, z.ZoneCode
FROM ZoneCodes z
INNER JOIN Cities c
ON c.CityID = z.CityID
WHERE (@CityName IS NULL) OR (CityName = @CityName)

If you can't do that - i.e. because this is freeform data, or because the data entry people might know the City but not the ZoneCode, etc., then all I can suggest is to make sure you're indexed properly:

CREATE INDEX IX_MyTable_City_ZoneCode
ON MyTable (City)
INCLUDE (ZoneCode)

Then your filter should be:

SELECT DISTINCT ZoneCode
FROM MyTable
WHERE (@City IS NULL) OR (City = @City)

...to get the ZoneCodes for a city, and:

SELECT FirstColumn, SecondColumn, ...
FROM MyTable
WHERE ((@City IS NULL) OR (City = @City))
AND ((@ZoneCode IS NULL) OR (ZoneCode = @ZoneCode))

---

If you have a composite index on (City, ZoneCode):

SELECT  *
FROM    mytable
WHERE   City = @City
        AND ZoneCode = @ZoneCode
UNION ALL
SELECT  *
FROM    mytable
WHERE   City = @City
        AND @ZoneCode IS NULL
UNION ALL
SELECT  *
FROM    mytable
WHERE   @City IS NULL
        AND @ZoneCode IS NULL

If you don't have one and cannot create it:

SELECT  *
FROM    mytable
WHERE   City = COALESCE(@City, City)
        AND ZoneCode = COALESCE(@ZoneCode, ZoneCode)

The first query assumes that you cannot filter on ZoneCode without specifying the City.

---

Conditional WHERE clauses in TSQL are pretty lousy. I'd construct different queries for each condition and run the appropriate one depending on user input.

---

Here is the best article I have ever seen about dynamic search in sql server: Dynamic Search Conditions in T-SQL