SQL statement - conditional WHERE

Question

I have a SQL Server 2005 stored procedure that executes a query. This stored procedure accepts three parameters. The parameters are as follows:

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

These parameters are used to request a list of clients. I want to basically do an "AND" if the parameter value is non-zero. However, I cannot do if-else at this time. How to add these sentences if the parameter value is not equal to zero. 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 

Answer 1

Connect those who have 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 these parameters, if it is zero, the check is mostly ignored.

Answer 2

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

Answer 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 find SO for all other questions asking the same ...

Answer 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)) 

Answer 5

there is no “right way”, it depends on many factors, here is an excellent article describing PRO and CON of each dynamic search method:

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

Answer 6

One of the methods:

 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) 

Answer 7

Try this with coalesce, I think this is 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) 

(Keep in mind - this will filter out records with zeros in CountyID or CityID.)