Self eliminated parameters

There are many common tasks that require

parameters which may be optional. One common example is a search that has many

different ways to locate the data such as name, age, address, gender, etc.

Another example is selecting groups of data via filtering. Again, this task can

have multiple criteria to select the data to be presented.

In both cases, not all of the criteria

would be specified each time the procedure is called. Coding a SQL statement

with one or more parameters missing in the WHERE clause causes a problem. A

common solution is the dynamic SQL and IF statement which allows the building

of a filter based on passing values, or in the case of fewer parameters using

an IF statement to call a query which a statically predefined filter where a

specific parameter has value.

In this article I would like to show a

different technique, lets call it a smart filter, which means - the WHERE

clause skips empty parameter(s) and excludes them from the query by themselves.

If you are not familiar with how the EXEC()

and sp_executesql procedures work, which are the dynamic SQL options, I would

recommend you read an article written by Erland Sommarskog The Curse and

Blessings of Dynamic SQL. This article has a comprehensive explanation for

dynamic SQL. It can be found at: http://www.sommarskog.se/dynamic_sql.html.

Now lets focus on the issue of how to

avoid dynamic SQL for this case. We need to let the WHERE clause know when the

parameter equals the default value do not use this as a filter condition.

To do that, you must use this syntax:

(COLUMN_NAME = @PARAM_NAME OR @PARAM_NAME = DEFAULT_VALUE)

Below I have provided you with a code

sample based on the Northwind database. Lets take one line from the sample and

see how the filter works.

The filter line is (Products.ProductName =

@prodname OR @prodname Is Null). Break filter line down in two parts. The first

part would be "Products.ProductName = @prodname attempt to retrieve rows

from result set and filter by ProductName." The second part would be

"If the value in the row does not match then parameter equal to default

which is always true." This way if the parameter remains as the default

value filter, look at the second part and exclude all parameter(s) with the default

values from the WHERE clause.

Note: In my coding techniques I

tried to limit my usage of the OR operator as much as possible, the dynamic SQL

would not be the better choice from the possibilities that are given. In the

final analysis, this technique could be one more choice for you to use.

Code samples:

-- 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
 
/*
 
declare @s varchar(1000)
set @s = '<root><ZipCode PostalCode = "87110"/>
 <ZipCode PostalCode = "67000"/> 
 <ZipCode PostalCode = "PO31 7PJ"/></root>'
  
exec spr_util_search2000'Chai', null, null, null
exec spr_util_search2000 null, 18, 35, null
exec spr_util_search2000 null, null, null, @s
exec spr_util_search2000 null, 10, 25, @s
exec spr_util_search2000 'Gorgonzola Telino', null, null, @s
 
 
*/
 
-- 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
/*
declare @s xml
set @s = '<root><ZipCode PostalCode = "87110"/>
 <ZipCode PostalCode = "60528"/> 
 <ZipCode PostalCode = "PO31 7PJ"/></root>'
exec spr_util_search2005 'Chai', null, null, null
exec spr_util_search2005 null, 18, 35, null
exec spr_util_search2005 null, null, null, @s
exec spr_util_search2005 null, 10, 25, @s
exec spr_util_search2005 'Gorgonzola Telino', null, null, @s
*/