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

Help with a multiple selection Parameter and a Case statement Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 4:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 4:45 PM
Points: 35, Visits: 184

I would like to create a multiple selection parameter to run in the WHERE clause for different conditions.

I would like the end user to be able to pick and choose which fields to do a 'Unknown' search for. These columns do not contain NULLs. Everything gets defaulted to unknown unless the Data Entry person makes a selection. This is part of a Data Entry Audit, trying to see what was left as the default unknown. I want to do a single Multiple drop down parameter because I have around 30 columns and doing a parameter for each is not practical.

An example Parameter @UnknownValues has a list of available columns

Label Value
-Date of Birth 01
-Name 02
-SSN 03
-Address 04

If the users checks any of these boxes in this drop down list I want those columns filtered in the query as look for the value "Unknown"

So if they check the box for SSN and Address I want the WHERE statement to say

WHERE SSN='Unknown' AND Address='Unknown'

I tried a CASE statement but my syntax must not be right or I am royally off track

WHERE t.Address=Case
When @UnknownValues='03' THEN t.address='Unknown'
END
AND t.SSN=CASE
WHEN @UnknownValues='04' THEN t.SSN='Unknown'
END

Thanks for any guidance
Thomas

Post #1521710
Posted Tuesday, December 10, 2013 10:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
I think the key here is knowing what's in @UnknownValues when the user selected SSN and Address. Something like '03,04' perhaps?

Probably you need to get a better handle on what you are trying to achieve by taking a look at this article: How to Design, Build and Test a Dynamic Search Stored Procedure and also reading the initial links in there to predecessor articles that explain why it is appropriate to use this approach.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1521743
Posted Wednesday, December 11, 2013 7:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 4:45 PM
Points: 35, Visits: 184
Thanks for that article, it looks like a very valuable tool. I'm going to try to do the hands on approach with it as it does seem to be pointing at what im trying to accomplish.
Post #1521902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse