Optimizing SQL Code by replacing IF..ELSE with CASE WHEN

Many times, we come across a need of writing different SQL SELECT queries in a stored procedure, just to seperate the WHERE clause logic depending upon if the parameter value is passed or not.

Lets consider following case -
Your stored procedure sp_GetCities has a parameter @CountryID.
Now, if the parameter is not null then you want to filter Cities based on passed CountryID.
But, if the parameter is Null then you need to return all cities irrespective of their country.

On many occasions, we write following logic in stored proc.


IF @CountryID is null // Or, IF ISNULL(@CountryID, 0) <= 0
BEGIN
SELECT ....
---No WHERE condition to filter countries-----
END
ELSE
BEGIN
SELECT ....
WHERE CountryID = @CountryID
END


There is one workaround to avoid writing multiple SELECT queries in similar situations.

Use of CASE..WHEN in SELECT statement


SELECT city_id, city_name
FROM tblCities
WHERE
1 = CASE WHEN ISNULL(@country_id, 0) <= 0 THEN 1 ELSE CASE WHEN @country_id = country_id THEN 1 ELSE 0 END END


Let us understand the WHERE clause -
If the right-hand side of the expression returns 1 then the city will be returned in resultset, otherwise it wont be.

If "@country_id" is not passed i.e. ISNULL(@country_id, 0), then obviously it will return 1. And in this case, all cities will be selected, as 1=1 is always true.
If "@country_id" is passed then if it matches with city's country_id (i.e., country_id = @country_id) then it will return 1, otherwise it will be 0.

We can use this upto any level, so we can definitely reduce number of SELECT queries in our code.