Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chiman
21 Posts |
 Posted - 2007-09-26 : 11:17:49
|
| Hi,Below is my scenario:Table name: "Items"Columns:Item Id,Item name,Item DescriptionI am implementing a search feature.There is SP for the same.The item Id is passed as a parameter and the records are fetched.If the Item id is passed as a integer value all works fine.For eg. @ItemId=1.Now the user would like to enter rangevalues on the front end screen.Like for eg.Item Id = 1-10 or user can enter Item Id=1-10 , 20-25, 30-50In such a case i am passing the contents as it is.I.e my @ItemId='1-10 , 20-25, 30-50'Now i need a function will will decode these appropriately and will return the result for these group of items.Kindly give me some directions in how to go about this scenario.ThanksRenu |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-26 : 11:24:01
|
| My first thought (and I'll probably be corrected by others on here) is to have your function create a #table with two columns (begin int, end int). Then have your function parse through and put values in your table like this.begin end1 1020 2530 50Next, have your function loop through the #table and construct your SQL statement like thisselect * from Items where (ItemID >=1 and ItemID <=10) or (ItemID >=20 and ItemID <=25) or (ItemID >=30 and ItemID <=50) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 11:55:13
|
| Its a nice end-user feature, might have to steal that!You can "split" the comma delimited list, and then you could further split any elements that were "-" ranges.You COULD combine those with a Tally table so that each & every value to match was in the Split-table.or you could keep them as ranges (and perhaps make the singletons range with themselves - e.g. 1-1, 5-5, 10-20and then match the data from there.Kristen |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-09-26 : 19:20:31
|
| My personal thought is that I'd probably allow a comma separated list. But, I would want the front end or middle tier to create the list of individual values. That way I could Split the string into a table and join to it. Just my 2 cents. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 02:01:36
|
| "front end or middle tier to create the list of individual values"Perhaps worth considering if that would take the overall list over 8,000 characters (which would be a useful limit for a UDF to be able to handle).Kristen |
|
|
|
|
|
|
|
|
Creating functions