Complex where clause?

Hi,

I have a stored procedure with a few parameters. One of them is @.ProviderParam and it defaults to Null. If a value is passed into that parameter when the sp is called, I need to include a check for the provider in the where clause, like this:

Where <some other stuff> AND Provider = @.ProviderParam

But if nothing is passed into that parameter (and it defaults to Null), I need to do nothing with provider in the where clause. The where clause would look like this:

Where <some other stuff>

I believe the solution is to use a CASE statement in the where clause somehow, but I'm not sure how to proceed. Can anyone please help?

Thanks.

One way is to do something like:

AND ( @.ProviderParam is null or
Provider = @.ProviderParam)

If your PROVIDER column is a non-null column you also might be able to use:

AND Provider = ISNULL (@.ProviderParam, Provider)

Again, the column must be a NOT NULL column or this filter will not work correctly whenever both @.ProviderParam and Provider are null.

|||

If I understand your issue correctly, you wish to optionally provide a value for the @.ProviderParam, use it in the WHERE clause if it is available, otherwise if it is NULL, ignore @.ProviderParam. If so, this may work for you:

AND Provider = coalesce( @.ProviderParam, Provider )

As Kent indicated, Provider MUST be a NOT NULL column for this approach to work properly.

|||The advantage of coalesce is that it will also work with DB2 whereas ISNULL will not.|||

Great stuff. Thank you, both.

Provider is a null column. DB2 is not a factor. Kent's 1st solution is really elegant. It doesn't involve a function call, which is efficient, and it's so simple. Just too much elegance for me to pass by! :)

Thanks, again, for these insights.

|||

Just a note to say that the other advantage of COALESCE is that it can take an arbitrary number of arguments and returns the first one that is not null.

SET A_Value = COALESCE(First_Choice, Second_Choice, Desperate_Choice, Default_Value)

This can be useful if you have something like 3 different names you could use before giving up and using 'UNKNOWN'.