When working with SQL Server, there are often tasks that require optional parameters. For example, a search query may have multiple criteria such as name, age, address, and gender, but not all of these criteria will be specified each time the query is executed. Similarly, when selecting groups of data via filtering, there may be multiple criteria to select the data, but not all of them will be provided.

One common solution to handle optional parameters is to use dynamic SQL and IF statements. This allows the building of a filter based on the passed values or, in the case of fewer parameters, using a statically predefined filter. However, there is another technique called “smart filters” that can be used to avoid dynamic SQL.

The idea behind smart filters is to skip empty parameters in the WHERE clause and exclude them from the query. This can be achieved by using the following syntax: (COLUMN_NAME = @PARAM_NAME OR @PARAM_NAME = DEFAULT_VALUE). Let’s take a look at an example to understand how this works.

Consider the following line of code from a sample query:

(Products.ProductName = @prodname OR @prodname Is Null)

In this line, the filter is divided into two parts. The first part attempts to retrieve rows from the result set and filter them by the ProductName. The second part checks if the value in the row does not match the parameter, in which case the parameter is equal to the default value (NULL). This ensures that if the parameter remains as the default value, the filter will exclude it from the WHERE clause.

Here is a code sample based on the Northwind database to demonstrate the usage of smart filters:

-- SQL Server 2000
USE Northwind
GO

CREATE PROCEDURE [dbo].[spr_util_search2000] 
@prodname varchar(50) = NULL, 
@lowprice float = NULL, 
@highprice float = NULL, 
@zipXML varchar(1000) = NULL 
AS 
BEGIN 
DECLARE @hDoc int 
DECLARE @zip TABLE (PostalCode varchar(15)) 
exec sp_xml_preparedocument @hDoc OUTPUT, @zipXML 
INSERT @zip SELECT PostalCode FROM OPENXML(@hDoc, 'root/ZipCode') WITH (PostalCode VARCHAR(20)) 
EXEC sp_xml_removedocument @hDoc 

SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.Products.UnitPrice, dbo.[Order Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.ShipVia, dbo.Orders.Freight, dbo.Orders.ShipName, dbo.Orders.ShipAddress, dbo.Orders.ShipCity, dbo.Orders.ShipRegion, dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry 
FROM dbo.Categories 
INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID 
INNER JOIN dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID 
INNER JOIN dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID 
WHERE (Products.ProductName = @prodname OR @prodname Is Null) 
AND (Products.UnitPrice BETWEEN @lowprice AND @highprice OR @lowprice Is Null) 
AND (Orders.ShipPostalCode IN (SELECT * FROM @zip) OR @zipXML Is Null) 
END 

-- SQL Server 2005
USE Northwind
GO

CREATE PROCEDURE [dbo].[spr_util_search2005] 
@prodname varchar(50) = NULL, 
@lowprice float = NULL, 
@highprice float = NULL, 
@zipXML xml = NULL 
AS 
BEGIN 
DECLARE @zip TABLE (PostalCode xml) 
INSERT INTO @zip (PostalCode) Values(@zipXML) 

SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.Products.UnitPrice, dbo.[Order Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.ShipVia, dbo.Orders.Freight, dbo.Orders.ShipName, dbo.Orders.ShipAddress, dbo.Orders.ShipCity, dbo.Orders.ShipRegion, dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry 
FROM dbo.Categories 
INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID 
INNER JOIN dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID 
INNER JOIN dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID 
WHERE (Products.ProductName = @prodname OR @prodname Is Null) 
AND (Products.UnitPrice BETWEEN @lowprice AND @highprice OR @lowprice Is Null) 
AND (Orders.ShipPostalCode IN (SELECT Code.value('(@PostalCode)', 'VARCHAR(20)') as PostalCode FROM @zip cross apply PostalCode.nodes('/root/ZipCode') as Zip(Code)) OR @zipXML Is Null) 
END 

By using smart filters, you can avoid the need for dynamic SQL and IF statements in certain scenarios. However, it’s important to note that this technique may not always be the best choice, depending on the specific requirements of your application.

In conclusion, smart filters provide an alternative approach to handle optional parameters in SQL Server queries. By skipping empty parameters in the WHERE clause, you can exclude them from the query and simplify your code. Consider using smart filters when appropriate to improve the efficiency and readability of your SQL queries.