Self Eliminated Parameters

,

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

Rate

3.25 (4)

Share

Share

Rate

3.25 (4)