Thursday, August 13, 2009

Code: SQL Dynamic Where Clause

Select ProductID, Name, Color, Size
From Production.Product
Where (Color = Coalesce(@Color, Color) or (@Color is null and Color is null))
And (
Size = Coalesce(@Size, Size) or (@Size is null and Size is null))

The SQL statement above works against the AdventureWorks database included with the SQL Server 2008 express download.  It’s not unreasonable to want your stored procedures to play well with your user interface, it makes the whole development process a little nicer.  So if you have a web page or windows form that has a handful of options a user can specify to filter data, using a dynamic where clause will prevent you from using string concatenation to build your query.  If you were to pass ‘blue’ into this query as the Color parameter and Null as the Size parameter, the query will return all Products with the color blue regardless of value (or null value) in the size column.


However the query can be simplified if your columns do not contain null values or if you don’t want data returned if it does contain a null value, just remove the or statements as seen below
Select ProductID, Name, Color, Size
From Production.Product
Where Color = Coalesce(@Color, Color)
And
Size = Coalesce(@Size, Size)

Similarly, if you want your query to match every parameter as passed in, retrieving null values only when you pass in a null for the parameter modify it like this
Select ProductID, Name, Color, Size
From Production.Product
Where (Color = @Color or (@Color is null and Color is null))
And (
Size = @Size or (@Size is null and Size is null))