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
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.
SELECT *
FROM Customer c
WHERE c.StateID=@StateID
AND c.CountyID= ISNULL(@CountyID, c.CountyID)
AND c.CityID = ISNULL(@CityID, c.CityID)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With