I have a stored procedure that takes 5 parameters:

1. start date
2. end date
3. call center
4. revenue band
5. product line

The procedure works great, and returns the write data for what a user has entered. What I am having trouble with is when I try to code for a user running the proc and wanting ALL the values in a column, for instance:

1. start date = january 1, 2005
2. end date = today
3. call center = calgary
4. revenue band = any of them
5. product line = pcs

To code this, I have to check to see if revenue band is 'ALL', and if it is I run a query that has no WHERE clause for revenue band, as opposed to if it wasn't 'ALL' I would run one that had a query for revenue band.

To check all possible combinations of 5 parameters is a ton of nested IF's, and no fun at all to code as it's massive. Surely this is a fairly common thing, what do I do here? I'm looking into building a dynamic SQL statement and running that - is that the best way to go?

Pat

Use what I've called the COALESCE trick.

Set the value of the parameter to NULL to mean "Don't care", i.e. you want any (or all) values, otherwise set it to the value you want to compare to.

In your example, then, the parameter value for 'Revenue Band' would be set equal to NULL, and all the rest set to whatever you want to test against.

Then, your WHERE clause uses COALESCE, as:

    ... WHERE RevenueBand = COALESCE(@RevenueBand, RevenueBand) ...

The way this works is that if the parameter @RevenueBand has a nonnull value, COALESCE returns the parameter value, so the result of the WHERE clause is to test the column value against the parameter value. But, if the parameter has the value NULL, then the COALESCE returns the column value. Thus, the WHERE clause tests the column to see if it is equal to itself, which of course is always true, so this is in effect a no-op, and all values are selected.

Note that if the column can have a NULL value, this won't work - you'll have to COALESCE both sides to some appropriate value outside the domain of possible values for the column if you want a NULL parameter to select NULL column values.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

And if your column does contain null values, I think this will work as well:

WHERE RevenueBand = @RevenueBand OR @RevenueBand IS NULL

Here you check whether the column searched in contains the value of @RevenueBand, or the entire parameter @RevenueBand is null (which is the case when you don't supply an explicit value)

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.