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
Very good. Worked for me.
Thanks. This was a cool old trick I’d used in the past and forgot about. Came in very handy right now.