a formula on a dataset filter?

Question

a formula on a dataset filter?

jbalbo

SSCertifiable

Points: 7583
More actions
November 14, 2013 at 12:50 pm

#395829

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

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[/url]

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/