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)