Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

a formula on a dataset filter? Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 12:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Hi

Using a query that will filter by location.
I added a first line that states "All" so if that is chosen they get all locations
if they choose a specific location then they only get that location

not sure how to setup the filter to exclude the "all"

Thanks In Advance

Joe
Post #1514453
Posted Thursday, November 14, 2013 2:09 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:52 PM
Points: 590, Visits: 911
You have a few options here. The first thing is that a query like this maybe impacted by bad parameter sniffing so test it with and without recompile.
1. Set @value = nullif(@value,'ALL') ... Location = coalesce(@value,Location) -- This should have at least statement level recompile
2. Dynamic SQL if @value <> 'ALL' set @whereStmt = N' where Location = @value '

There are a lot of different options actually, but I would consider the first two and see how performance is afterwards.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1514478
Posted Thursday, November 14, 2013 2:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Hi Keith

Thanks for getting back..

Sorry I'm a bit confused, but I think it may be better to insert it in a where in the query?

Thinking of something like..

WHERE
(DATEDIFF(DD, Client.EffDate, Client.Expdate) IS NULL) OR
(DATEDIFF(DD, Client.EffDate, Client.Expdate) > 0)
and
case
when @location <>'All' then @location = location.code end


Really not sure of the last part?

I dont want to limit any location if 'ALL' is chosen..

Thanks Again


Post #1514487
Posted Thursday, November 14, 2013 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,481, Visits: 12,338
This is a type of catch all query. Check out this article for an explanation of how to handle this type of thing.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514489
Posted Thursday, November 14, 2013 3:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Thanks Sean & Keith,

That article is great.
I used the typical Catch-All and it worked

I may try to do it dynamically (looks fun, never did it)

Thanks
Joe
Post #1514507
Posted Thursday, November 14, 2013 3:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,481, Visits: 12,338
jbalbo (11/14/2013)
Thanks Sean & Keith,

That article is great.
I used the typical Catch-All and it worked

I may try to do it dynamically (looks fun, never did it)

Thanks
Joe


It will perform better and you will learn a ton of stuff doing it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514510
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse