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)