SQL Server Portal

Few days ago, I received a query from one of my blog readers inquiring how to implement a conditional where clause in a SQL Server query / stored procedure. His requirement was quiet simple. He said that if he passes any filter value to the query / procedure, it should consider that filter otherwise query should ignore that filter and return all records.

Given below is the script.
SAMPLE :

USE AdventureWorks2012
GO
--DROP PROCEDURE Usp_sample
--GO
CREATE PROCEDURE Usp_sample
@City nvarchar(30),
@CountryRegionName nvarchar(50)
AS

SELECT *
FROM
HumanResources.vEmployee
WHERE (1=(CASE WHEN @City IS NULL THEN 1 ELSE 0 END) Or [City]=@City)
AND (1=(CASE WHEN @CountryRegionName IS NULL THEN 1 ELSE 0 END)
Or [CountryRegionName]=@CountryRegionName)
GO

EXAMPLE 1 : (WITHOUT any filter)
In this example, I did not pass any filter to the stored procedure, so the stored procedure ignored all filters and it returned all records as expected.

USE AdventureWorks2012
GO
EXEC Usp_sample @City=NULL,@CountryRegionName=NULL
GO

EXAMPLE 2 : (With ONE filter)
In this example, I passed only one filter to the stored procedure and it returned records on the basis of only one filter and ignored the second filter.

USE AdventureWorks2012
GO
EXEC Usp_sample @City='Renton',@CountryRegionName=NULL
GO