September 23, 2013 at 11:50 am
Hi,
I'm facing a task how to deal with unknown number of user entered params, could be from 1 to 10.
User actually will type this into text box comma separated, that what they want, we can't go with with param list, as it tooooooo big, (1000+)
And I don't know how put this into my code for SSRS report, it not desired to be dynamic but if not choice we probably can go with too.
So, user want manually enter list of params and then I need to find WHERE all of them are true,
@param1 = 'Alpha, Bravo, Charlie, Delta' --up to 10 max
select * from T1 where
c1 = 'Alpha'
or c1 = 'Bravo'
or C1 = 'Charlie'
or C1 = 'Delta'
Not sure if I can use arrays in my situation, I'm on TSQL 2008 and SSRS, that's it. The only way I see it now to split by ',' into param list (or load it into table), then make max number of WHERE .
something like 'Where c1 = param1a' and param1a is not NULL
and ..... up to max 10 occurences...
Thanks to all, hope somebody can push me to the right place how to do this nicely.
Mario
September 23, 2013 at 12:04 pm
Check out the link in my signature about splitting strings. In there you will find a way to do this very easily and fast.
Your code might be as simple as the following:
select *
from T1
join dbo.DelimitedSplit8K(@param1, ',') s on s.Item = T1.c1
_______________________________________________________________
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/
September 24, 2013 at 8:38 am
Sean has provided probably the best T-SQL solution to your problem. If you aren't using stored procedures for your report in SSRS, then I would consider doing the splitting in Custom Code in the report. Something like this:
Function CreateWhere(ByVal list as String, ByVal colName as String) as String
Dim params as String() = list.Split(new [Char](){","})
Dim where as String = "Where " & colName & " = "
Dim counter as Integer = 1
For Each s as String in params
where = where & "'" & s.Trim() & "'"
If counter < params.Length Then
where = where & " OR " & colName & " = "
End IF
counter = counter + 1
Next s
RETURN where
END FUNCTION
Then your query in your dataset is an expression like this:
="Select * from table1 " + Code.CreateWhere(Paremeters!param1.Value, "C1")
Which will return a query like this:
Select * From table1 Where C1 = 'Alpha' OR C1 = 'Bravo' OR C1 = 'Charlie' OR C1 = 'Delta'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply