Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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 delimeted column. i am planning to fix this by creating a seperate table but for now i need a workaround. I Would really appriciate 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 its something like 3 then it should return row 3.

my real stored proc 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 paramter but only see if the service entries exist in the service entry parts. if the param 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) 
share|improve this question
    
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 '12 at 15:06

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
@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 '12 at 14:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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