SQL - Conditional WHERE clause

问题:

I have a SQL Server 2005 stored procedure that performs a query. This stored procedure takes in three parameters. The parameters are as follows:

@StateID as int,
@CountyID as int,
@CityID as int

These parameters are used to query a list of customers. I want to basically do a "AND" if the parameter value is not null. However, I cannot do an if-else at this time. How do I add these clauses if the parameter value is not null. In other words:

SELECT
  *
FROM
  Customer c
WHERE
 c.StateID=@StateID 
 -- AND c.CountyID=@CountyID IF @CountyID IS NOT NULL
 -- AND c.CityID=@CityID IF @CityID IS NOT NULL

回答1:

Couple those with some OR statements:

SELECT *
FROM
    Customer c
WHERE
    c.StateID=@StateID
    AND ( c.CountyID=@CountyID OR @CountyID IS NULL )
    AND ( c.CityID=@CityID OR @CityID IS NULL )

For each of those parameters, if it is null, then the check is basically ignored.

回答2:

SELECT * 
FROM Customer c 
WHERE c.StateID=@StateID 
AND c.CountyID= ISNULL(@CountyID, c.CountyID) 
AND c.CityID = ISNULL(@CityID, c.CityID)

回答3:

 c.StateID=@StateID 
 AND
 c.CountyID = ISNULL(@CountyID, c.CountyID)
 ...

Use IF statements

Or

 c.StateID=@StateID 
 AND
 @CountyID IS NULL OR c.CountyID = @CountyID)
 ....

Or dynamic SQL

Or search SO for all the other question asking the same...

回答4:

 SELECT * FROM Customer c WHERE (c.StateID=@StateID) AND ((c.CountyID=@CountyID) OR (@CountyID IS NULL)) AND ((c.CityID=@CityID) OR (@CityID IS NULL))

回答5:

there is no "right way", it depends on many factors, here is a great article describing the PROs and CONs of each way to have dynamic search conditions:

http://www.sommarskog.se/dyn-search.html

回答6:

One way:

SELECT * 
FROM Customer c 
WHERE c.StateID=@StateID 
    AND (@CountyID IS NULL OR c.CountyID=@CountyID)
    AND (@CityID IS NULL OR c.CityID=@CityID)

Another way:

SELECT * 
FROM Customer c 
WHERE c.StateID=@StateID 
    AND c.CountyID=ISNULL(@CountyID, c.CountyID)
    AND c.CityID=ISNULL(@CityID, c.CityId)

回答7:

Try it with coalesce, I think it's a very elegant, clean approach:

SELECT *
FROM Customer c
WHERE
 c.StateID=@StateID 
 AND c.CountyID = COALESCE(@CountyID, c.CountyID)
 AND c.CityID = COALESCE(@CityID, c.CityID)

(Be aware though - this will filter out records with nulls in CountyID or CityID.)