Tuesday, February 19, 2013 10:55 PM
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?!
- Edited by SqlCraze Tuesday, February 19, 2013 11:37 PM
Wednesday, February 20, 2013 12:25 AM
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!
Wednesday, February 20, 2013 5:02 AMI 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?
- Edited by SqlCraze Wednesday, February 20, 2013 5:03 AM
Wednesday, February 20, 2013 5:40 AM
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:
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!
- Marked As Answer by SqlCraze Wednesday, February 20, 2013 7:48 AM
Wednesday, February 20, 2013 7:45 AM
I just realized you have the following:
Did you mean to have the equal sign in there?
Wednesday, February 20, 2013 7:49 AMThank you so much. Looks like that was the problem. By default, my date parameters in my report should be NULL for now?
Wednesday, February 20, 2013 7:51 AM
Typo. Its actually @Status Varchar(Max)
Wednesday, February 20, 2013 7:53 AM
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.