0

I have a column in a table with sample data as follows:

ServiceTypeIds
 7,1
 1,9 
 1
 9
 4
 7
 7

In my Where clause I use the following to search on it:

WHERE (@ServiceTypes IS NULL 
   OR CHARINDEX(','+CAST(SEP.ServiceTypeIDs as VARCHAR(255))+','
              , ','+@ServiceTypes+',') > 0)))

If my param @ServieTypes equals '1,9', then I get results, but when it's @ServiceTypes = '1' or just '9', then I don't recieve anything back. I'm stuck and tried the above.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
tam tam
  • 1,870
  • 2
  • 21
  • 46
  • Please don't take this the wrong way, but your situation is why delimited strings is frowned upon. One column, one value. The query performs better when 3rd normal form design is used. If at all possible, redesign the table to be proper. It will save you so much in the long run. – OMG Ponies Jul 30 '12 at 01:36
  • I understand, I inherited this application, At that time it was not expected to search based on this criteria from a page. im using mvc and if i create a new table, i have to modify code in many places so due to time constraints I just need a fix for the stored proc now.. – tam tam Jul 30 '12 at 01:39
  • I'd make a [TVF](http://msdn.microsoft.com/en-us/library/ms191165%28v=sql.105%29.aspx) to convert the values into a table (table variable, but close enough). The columns would just be the primary key, and a `serviceTypeID`. You could join on the primary key, and search against the column. It'd also make transitioning the values to an actual table easy because the code would need very little alteration. – OMG Ponies Jul 30 '12 at 01:54
  • You may find this useful: http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql – Niloct Jul 30 '12 at 01:59

2 Answers2

3

I think you have your parameters reversed:

WHERE (@ServiceTypes IS NULL 
       OR CHARINDEX(','+CAST(@ServiceTypes as VARCHAR(255))+',',
                    ','+SEP.ServiceTypeIDs+',') > 0)))

This method works, but only when you are looking for one item (or are lucky and the two items happen to be in the same sequence).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Alternate method

WHERE (@ServiceTypes IS NULL  
   OR ','+@ServiceTypes+',' LIKE '%,'+CAST(SEP.ServiceTypeIDs as VARCHAR(255))+',%')
Madhivanan
  • 13,470
  • 1
  • 24
  • 29