SQL for RDL using wildcards with NULL 1

[BeachSandGuy]

Hi,
I'm a relative beginner with SQL and reporting and can do some basic reports using SQL and the Report Designer in SQL Server 2005 Visual Studio tools.

I have a query to pull results that is relatively simple:
Select *
From dbo.RouteTable
Where
EffectiveDate >=@StartDate AND
EffectiveDate <=@EndDate AND
Policy in (@Policy) AND
TFN in (@TFN) AND
GroupName like '%' + @GroupText + '%'

My problem is that in the report that runs based off of this, I would like to allow the @GroupText variable to be blank, which would then allow all records to be returned. However when blank, I get nothing. How can I make this so it would allow text entry or NULL to bring all records back?
In Report parameters, i do have datatype set as String, have the Allow null value and Allow Blank value checked. Default value is set to Null.
Thanks.

Ian

 

[gmmastros]

Select *
From dbo.RouteTable
Where
EffectiveDate >=@StartDate AND
EffectiveDate <=@EndDate AND
Policy in (@Policy) AND
TFN in (@TFN) AND
[!](@GroupText Is NULL OR [/!]GroupName like '%' + @GroupText + '%'[!])[/!]

When you pass in null for @grouptext, the left side of that expression will evaluate to true, so it won't matter what the right side evaluates to. If it's not null, then the right side will evaluate to true for some of the rows, and only those rows will be returned.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BeachSandGuy]

Wow, that seemed so simple, and reading it, makes perfect sense, just didn't think to evaluate right side first. Tested, worked great. Thank you so much for your help and very quick reply, George!

 

[gmmastros]

No problem.

Just remember, when you mix and's with or's in your where clause, you really should use parenthesis so that the evaluation is the way you expect it to be. For example, removing the parenthesis is likely to give you different data. Just be careful.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom