CASE statement in a where clause for multi valued parameter

Question

Hey all,

I have a stored procedure that forms my report body. I have 3 parameters. FromDate, ToDate and Status.

Some of the fields are Id,RedDate,Value,Status

I have declare the params as:

@FDate DATE = NULL,
@TDate DATE = NULL,
@Status = VARCHAR(MAX)

In my select statement I have something like this:

Select Id,Value,RedDate,Case when rf like '%Renew%' then 'R' else [State] End As [Status]  From MyTable
Where
CONVERT(DATE,RedDate) BETWEEN Coalesce(@FDate,RedDate) AND Coalesce(@TDate,RedDate)
OR @FDate IS NULL
OR @TDate IS NULL
And (Case When rf like '%Renew%' then 'R' else [State] End IN
(SELECT VALUE FROM dbo.ufn_Split(@Status,',')) OR '!All' IN (@Status))

Now, RedDate will have value only if st='R', if the value is anything other than 'R', then RedDate value is NULL. That's the reason I have declared the date parameters as NULLS.

Problem is, when I execute my stored procedure by passing the following values it gives me all the results and not the ones that I actually want. Here is how I execute my stored proc:

Exec [dbo].[Test] NULL , NULL, 'A'

So, even if I am passing the value of "Status" parameter as just 'A', still it gives me all the results that include 'R','U','C' statuses as well. I was expecting to get data only related to the Status='A'.

Can someone direct me? When i just pass the "Status" parameter as "A" and no dates, then results are perfectly fine. Looks like, there is an issue with passing NULL dates?

Also, since Statuses like "A", "C", "U" don't have any RedDates, that might be an issue?!

Answer 1

You are missing parentheses in your WHERE clause.

Because you are using OR in the WHERE clause, whenever any of the OR expression evaluates to true, the data will be returned.  For example, if @FDate IS NULL, it will always return that data and not even bother to check the value of @TDate and will also not bother to check your last CASE statement.

Make sure your fix the parentheses as needed and check carefully....

Hope this helps!

Answer 2

I am sorry, but I can't figure out, where the parenthesis are missing ! And do you think its OK if I have set my date  params as NULLS?

Answer 3

It's ok to have your date params as NULL if your business rule allows for them to be NULL.  http://msdn.microsoft.com/en-us/library/ms189330(v=sql.105).aspx for more information regarding parameter default values.

If I'm guessing what you are trying to do correctly, perhaps what you meant to say is something more like the following:

Select Id,Value,RedDate,Case when rf like '%Renew%' then 'R' else [State] End As [Status]  From MyTable
Where
(
CONVERT(DATE,RedDate) BETWEEN Coalesce(@FDate,RedDate) AND Coalesce(@TDate,RedDate)
   OR @FDate IS NULL
   OR @TDate IS NULL
)
And 
(
(Case When rf like '%Renew%' then 'R' else [State] End IN
(SELECT VALUE FROM dbo.ufn_Split(@Status,',')) 
OR '!All' IN (@Status))
)

I tried to show this so that the new parentheses are in their own lines -- hopefully it'll show up correctly.  In other words you want to check both the date parameters as a whole AND the @Status value.  If not, you may have to place the parentheses so that the WHERE clause matches your business rules intentions.

Basically, keep in mind that when you have something as follow: "expression1 OR expression2 OR expression3 AND expression4 OR expression5"... if expression1 is true, the whole statement will be true and the other expressions may not even get evaluated (short-circuit).  If expression2 is true, the whole statement is true and the rest of the expressions may not get evaluated.  This is also the case for expression5, etc.

On the other hand, if you have "(expression1 OR expression2 OR expression3) AND (expression4 OR expression5)" then both (expression1 OR expression2 OR expression3) has to be equate to true AND (expression4 OR expression5) has to equate to true.

Hope this helps!

Answer 4

I just realized you have the following:

@Status = VARCHAR(MAX)

Did you mean to have the equal sign in there?

Answer 5

Thank you so much. Looks like that was the problem. By default, my date parameters in my report should be NULL for now?

Answer 6

Typo. Its actually @Status Varchar(Max)

Answer 7

Glad to be of help!  

As for your question: "By default, my date parameters in my report should be NULL for now?"

That depends on your business rules.  The default value can be NULL but when and why should it be NULL.. and whether the default value should be NULL is something that you and your business logistics need to determine.