Create Dynamic WHERE clauses with only CASE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create Dynamic WHERE clauses with only CASE

Author

Topic

CMartin
Starting Member

13 Posts

Posted - 2002-08-04 : 00:29:23

This is an example on how you can use dynamic WHERE clauses in your store procedures by using ONLY the CASE system function and without the need to write dynamic SQL.

Imagine the following situation for Northwind database:

You want users to be able to search the customers database by Country and/or Contact Title.

When searching by country they can use one of these options:
- See customers from all countries
- See customers from a specific country
- See customers from all countries EXCEPT the selected country

When searching by Contact Type they can use one of these options:
- See all type of contacts
- See only contacts of the selected type

Store procedure will receive three parameters:
@Country - NULL/BLANK (all) or the Country Name
@ExcludeCountry - 0=No / 1=Yes
@ContactTitle - NULL/BLANK (all) or the Contact Title

We need @ExcludeCountry to know if the country is to be excluded from the results.
Obs: This parameter must be controled before passing it to the store procedure to avoid that a user exclude all countries ;)

The solution without the need to write and use dynamic SQL is to use CASE in the WHERE clause.

Here's the solution (that you can use in SQL Query Analyzer):


--- START CODE ---

USE Northwind

DECLARE @ExcludeCountry bit
DECLARE @Country varchar(20)
DECLARE @ContactTitle varchar(30)

/*
@Country        - NULL/BLANK (all) or the Country Name
@ExcludeCountry - 0-No / 1-Yes
@ContactTitle   - NULL/BLANK (all) or the Contact Title
*/

----------------------------------------------------------------
--- CHANGE THE FOLLOWING PARAMETERS TO GET DIFFERENT RESULTS ---
----------------------------------------------------------------

SET @ExcludeCountry = 1  -- 0=No / 1=Yes

SET @Country = 'USA'
-- Other Countries: Belgium, Brazil, Canada, Portugal, UK

SET @ContactTitle = 'Owner'
-- Other Titles: Marketing Manager, Sales Representative, Accounting Manager

----------------------------------------------------------------

SELECT Country, ContactName, ContactTitle, CustomerID
FROM Customers
WHERE @ExcludeCountry =
	CASE
	     /* Include all countries */
		WHEN @ExcludeCountry = 0 AND @Country IS NULL OR LEN(@Country) = 0 THEN 0

	     /* Include only the specified country */
		WHEN @ExcludeCountry = 0 AND @Country IS NOT NULL AND Country = @Country THEN 0

	     /* Exclude the specified country */
		WHEN @ExcludeCountry = 1 AND @Country IS NOT NULL AND Country <> @Country THEN 1
	END


AND 1 =
	CASE
	     /* Include all contact titles */
		WHEN @ContactTitle IS NULL OR LEN(@ContactTitle) = 0 THEN 1

	     /* Include only contacts with the specified title */
		WHEN @ContactTitle IS NOT NULL AND ContactTitle = @ContactTitle THEN 1
	END

ORDER BY Country, ContactName

GO

--- END CODE ---

Note: In the ContactTitle you could use the COALESCE Function solution and get the same results, but this is an example to demonstrate some ways to use CASE in dynamic WHERE clause.

The above code will give you all Contacts that are 'Owners', from all countries, EXCEPT 'USA'

You can adapt this solution to be used in almost situations that requires dynamic WHERE clauses and start creating more powerfull queries.

One of the best things when using this solution is that SQL Server can reuse the execution plan, saving the overhead of recompiling the SQL statement everytime is called.

I would like to have your comments about this solution.

Hope this helps someone,

Carlos Martinho

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources