SQL SERVER 2008 TVF OR CHARINDEX to search column with comma

Question

ID    BlahColumn
1       2
2       NULL
3       2,3
4       4,5,6
5       1,2 
6       1
7       1 

Hello, I am using the following as part of the where clause and using charindex is not working correctly to search the above comma delimited column. i am planning to fix this by creating a separate table but for now i need a workaround. I Would really appreciate your help:

WHERE(@Param IS NULL OR
      CHARINDEX(','+@Param+',', ','+CAST(BlahColumn as VARCHAR(255))+',') > 0)))  

IF @Param = '1,2' then it should return rows 1,3,5,6,7 and if it's something like 3 then it should return row 3.

my real stored procedure looks like this

 (SELECT DISTINCT  
         S.ID, S.ServiceDateTime, S.ReasonForFailure, S.ActionTaken, S.VerificationPerformed, S.TSBNumber, S.ISANumber, SS.SystemFullName AS SystemName,   
                      U.FullName AS TechnicianName, S.ErrorDescription AS ErrorDescription, S.ClosedDate, S.ServiceRequestClosed,   
                      S.ServiceHours, S.ServiceMinutes, S.PartDescription, S.RequestAddParts AS AdditionalPartsRequested, ISNULL(S.Reconciled, 0) AS Reconciled,  
                          (SELECT     FullName  
                            FROM          dbo.[User] AS U1  
                            WHERE      (ID = S.CreatedBy)) AS CreatedBy, S.CreatedDate,  
                          (SELECT     FullName  
                            FROM          dbo.[User] AS U1  
                            WHERE      (ID = S.UpdatedBy)) AS UpdatedBy, S.UpdatedDate  
   FROM ServiceEntry AS S INNER JOIN     
    dbo.[User] AS U ON U.ID = S.TechnicianID INNER JOIN  
                      dbo.System AS SS ON SS.ID = S.SystemID  LEFT OUTER JOIN  
                      dbo.ServiceEntryErrorCode AS SEEC ON SEEC.ServiceEntryID = S.ID  
   WHERE (S.RunLogEntryID is not null) AND (S.Reconciled=@ReconciledValue) AND (ServiceDateTime between @StartDate and @EndDate)    
   and S.ID IN(SELECT SEP.ServiceEntryID FROM ServiceEntryPart SEP inner join Part ON SEP.PartID = Part.ID
   WHERE
  (@ServiceTypes is null or       
   CHARINDEX(','+@ServiceTypes+',', ','+CAST(SEP.ServiceTypeIDs as VARCHAR(255))+',') > 0) 
   AND
(@ActivePart is null or @ActivePart = Part.Active)

@gbn im using your solution first im trying to just run the query and then will include your solution in my above query:

The following works great, declare @param nvarchar(100) = null SET @param = '1,4'

SELECT *
FROM 
  ServiceEntryPart M
  CROSS APPLY
  dbo.SplitStrings_Moden(M.ServiceTypeIDs, N',') M2
  JOIN dbo.SplitStrings_Moden(@param, N',') P ON M2.Item = P.Item

SO i include the above query in my stored procedure and do something like this but, what im trying to do is find all service entries which have service parts, and the where clause is optional, so for instance if my @servicetypes param is NULL then I would not search against the parameter but only see if the service entries exist in the service entry parts. if the parameter is not null then i will search against the service type ids. please help, i have tried and not been able to find the correct result

S.ID IN(

SELECT DISTINCT ServiceEntryID
FROM 
  ServiceEntryPart M
  CROSS APPLY
  dbo.SplitStrings_Moden(M.ServiceTypeIDs, N',') M2
  JOIN dbo.SplitStrings_Moden(@ServiceTypes, N',') P ON M2.Item = P.Item
  INNER JOIN Part ON M.PartID = Part.ID
  WHERE @ActivePart is null or @ActivePart = Part.Active) 

Answer 1

You need a split TVF on both sides to make it a JOIN

SELECT DISTINCT
   M.ID
FROM
   MyTable M
   CROSS APPLY
   dbo.SomeSplitTVF(M.Blahcolumn) M2
   JOIN
   dbo.SomeSplitTVF(@parameter) P ON M2.value = P.value

There are several solutions for "dbo.SomeSplitTVF" on SO, but you could start with this article by our own Aaron Bertrand