July 5, 2011 at 5:44 am
I've one table with records like this:
Col1Col2Col3
Task1A2001-01-01
Task1B2001-01-01
Task1C2001-01-01
Task2A2001-01-05
Task2B2001-01-05
Task3A2001-01-10
Task3C2001-01-10
Task3D2001-01-10
Task3E2001-01-10
Task3F2001-01-10
If my input @input = 'A, B', then my output should be
Col1Col3
Task12001-01-01
Task22001-01-05
It means, I should get all the Col1 and Col3 values for the list of values provided in @input variable. If it contains three values, then I should get tasks having all of the three values and NOT any of the three.
July 5, 2011 at 5:53 am
You've got three options, in ascending order of preferability:
(1) Use dynamic SQL
(2) Search this site for "splitter function" and use that two break your input string into a set of values
(3) Recode your application so that it passes in a set of values instead of a delimited string.
John
July 5, 2011 at 6:05 am
If I say i have a set of values but still need informaiton in such a way that only those Tasks containing all those values must come.
July 5, 2011 at 9:51 am
Can anyone suggest ? All I need is to find the optimized way to get only those Tasks having all the values mentioned in input parameter for Col2
July 5, 2011 at 11:33 am
I second Johns alternatives and would prefer option 2.
To be a little more specific regarding the splitter function: search for DelimitedSplit8K.
The basic concept: shred the comma delimited list into a table with one row per value and either use that table as an inner join object or use the CROSS APPLY approach, if you're familiar with it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply