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
*/
