Help with this Where Clause! (challenge)

Question

Post reply

Help with this Where Clause! (challenge)

manssourianm

Mr or Mrs. 500

Points: 556
More actions
November 18, 2011 at 11:19 am

#98089

hi,
we have ten lines, numbered 1-10.
#1 through #5 are bus
#6 through #8 are rail
#9 and #10 are bus
i have a variable @line, so the user can select a specific line.
for the variable @line, i'd like the user to be able to select:
-one line (by typing the specific line in the parameter)
-all the bus lines (by typing 'All Bus' for the parameter)
-all the rail lines (by typing 'All Rail' for the parameter)
-all the lines (by typing 'All' for the parameter)
any ideas?
thanks!

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

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

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

Gail has a great blog post on this very topic. 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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Dynamic SQL. See my blog post. It can be done in a single statement but its performance will be comparable to molasses in a Canadian winter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 3

i know how to do the 'All' option, i do it like this:

where line = case when @line = 'All' then line else @line end

i just don't know how to add in the 'All bus' or 'All rail' parts

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

GilaMonster (11/18/2011)
Dynamic SQL. See my blog post. It can be done in a single statement but its performance will be comparable to molasses in a Canadian winter.