December 10, 2013 at 4:19 pm
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
***SQL born on date Spring 2013:-)
December 10, 2013 at 10:56 pm
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 [/url] and also reading the initial links in there to predecessor articles that explain why it is appropriate to use this approach.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 11, 2013 at 7:32 am
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.:-)
***SQL born on date Spring 2013:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply