Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Self Eliminated Parameters

By Alex Grinberg,

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 */
Total article views: 9071 | Views in the last 30 days: 1
 
Related Articles
FORUM

Filter Group By Total by Parameter

Filter Group By Total by Parameter

FORUM

Filter a report based on a non queried parameter

Filter different fields on multiple values based on output from parameter

FORUM

Parameter to Parameter

Parameter Values

FORUM

Parameter Dependency

Parameter Dependency

FORUM

Filter Graph by multi valued parameter

Multi-valued Parameter for sub-report Graph

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones