Author |
Topic |
alxtech
Yak Posting Veteran
66 Posts |
Posted - 2007-04-26 : 11:38:14
|
I have a field in a database called categories with values separated by commas, example.Arts,Education,Sports,PoetryThis field was saved into the table thourgh a list box selection element.now in my application the user selects values from a list box and those values are compare to the database field categories. If one of those values selected in the search appears in the categories field that row is selected for display. exampleeventID Categories13 Arts,Sports,Poetry14 Sports,Poetry,Lecture,Math15 Arts,Religion,Math,Geography16 Sports,Poetry,Lecture,HistoryIn application user selects from list box:Arts,Religion,Historyso the records to return are:13 15 16currently my application is accepting one value from the list box and it works fine, i am using the '%like with wildcard%' option, but I need to pass and compare multiple values. |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
alxtech
Yak Posting Veteran
66 Posts |
Posted - 2007-04-26 : 11:58:58
|
can you give me a hint, i cannot get it to work, this is my store procedure code:CREATE PROCEDURE dbo.sp_get_category (@multiCategory VARCHAR(1600)=NULL, @SQL VARCHAR(1600) =NULL)ASBEGINIF(@multiCategory IS NULL) BEGIN SET @SQL='SELECT * FROM tblCategory END ELSE SELECT * FROM tblcategory WHERE categoryName like '%' + @multiCategory + '%' EXEC(@SQL)ENDGO |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-26 : 18:46:45
|
sorry should be using fnParseListselect c.*from tblcategory c inner join dbo.fnParseList(' ', @multiCategory) mon c.categoryName like m.Data + '%' KH |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-26 : 18:55:09
|
[code]-- Prepare sample dataDECLARE @Sample TABLE (EventID INT, Categories VARCHAR(99))INSERT @SampleSELECT 13, 'Arts,Sports,Poetry' UNION ALLSELECT 14, 'Sports,Poetry,Lecture,Math' UNION ALLSELECT 15, 'Arts,Religion,Math,Geography' UNION ALLSELECT 16, 'Sports,Poetry,Lecture,History'DECLARE @UserPass VARCHAR(99)SET @UserPass = 'Arts,Religion,History'-- Show the expected outputSELECT s.EventID, COUNT(*) AS HitsFROM dbo.fnParseList(',', @UserPass) AS xINNER JOIN @Sample AS s ON ',' + s.Categories + ',' LIKE '%,' + x.Data + ',%'GROUP BY s.EventIDORDER BY s.EventID[/code]Peter LarssonHelsingborg, Sweden |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-26 : 18:59:21
|
Or with the new SQL Server 2005 CROSS APPLY operator-- Show the expected outputSELECT s.EventID, COUNT(*) AS HitsFROM @Sample AS sCROSS APPLY dbo.fnParseList(',', s.Categories) AS yINNER JOIN dbo.fnParseList(',', @UserPass) AS x ON x.Data = y.DataGROUP BY s.EventIDORDER BY s.EventID Peter LarssonHelsingborg, Sweden |
 |
|
|
|
|