2
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) 
1
  • 1. use M.ServiceTypeIDs 2. you haven't given all parameters for the 1st TVF (as you did the 2nd) 3. I used "value" as a placeholder: what does the TVF use as the column name for it's output? C'mon, basic errors here that the errors describe exactly...
    – gbn
    Jul 30, 2012 at 15:06

1 Answer 1

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

1
  • @user1475788: you should work my solution into yours then. I've shown you how to match any item from 2 CSVs in SQL.
    – gbn
    Jul 30, 2012 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.