How to remove redundant conditions in IN query Sql

Question

I have this kind of query. But i need to optimize this query so how can omit redundant conditions with same split function.

DECLARE @Filter nvarchar(20)
SELECT @Filter ='5,22,3'

SELECT * FROM    Employee e
             WHERE e.code IN 
             (
             CASE WHEN((SELECT count(*) FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d 
                                  WHERE d.splitdata IN (5, 16, 20, 23, 33, 49, 62, 90, 91, 92, 93, 94))>0) THEN 5 ELSE 0 END 
             ,CASE WHEN((SELECT count(*) FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d 
                                  WHERE d.splitdata IN (22, 18))>0) THEN 46 ELSE 0 END
             ,CASE WHEN((SELECT count(*) FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d 
                                  WHERE d.splitdata IN (3, 28))>0) THEN 3 ELSE 0 END
             )

Best optimization would be to stop using strings containing commas as some kind of container for multiple data values - SQL Server has data types *designed* to hold multiple values - tables and xml. — Damien_The_Unbeliever, Mar 16 '17 at 08:04

Venu · Answer 1 · 2017-03-16T12:41:06.867

As @Damien_The_Unbeliever said, avoid split strings as table of values. You do not need to split the same string multiple times. Instead you can use a temporary table variable.

DECLARE @SplitStrings TABLE
(
    splitdata int
)

INSERT @SplitStrings
SELECT splitdata FROM dbo.FNSPLITSTRING(SUBSTRING(@Filter,1,LEN(@Filter)-1), ',') d

DECLARE @EmployeeCodes TABLE
(
    splitdata INT,
    code int
)

INSERT @EmployeeCodes (splitdata, code)
VALUES (5, 5), (16, 5), (20, 5), (23, 5), (33, 5), (49, 5), (62, 5), (90, 5), (91, 5), (92, 5), (93, 5), (94, 5), 
       (22, 46), (18, 46),
       (3, 3), (28, 3)


SELECT e.* 
FROM Employee e
JOIN @EmployeeCodes ec
ON e.code = ec.code
JOIN @SplitStrings ss
ON ec.splitdata = ss.splitdata

I hope this is the direction you are looking at.

Note: Assuming that you do not need 0 as employee code.

@ZoharPeled Yes you right . Actually `@Filter` is the other no of codes. And on the basis of this i need specific employee. like EmployeeCode 46,3 etc — shaair, Mar 16 '17 at 10:06

score 0 · Accepted Answer · answered Mar 20 '17 at 11:14

I have done new way. Less complex and omit redundant code.

DECLARE @Filter nvarchar(20)
SELECT @Filter ='5,22,3'

SELECT Distinct e.EmployeeId FROM Employee e
CROSS JOIN dbo.fnSplitString(@Filter, ',') AS d
WHERE 
(e.code = 5 AND d.splitdata IN ('5', '16', '20', '23', '33', '49', '62', '90', '91', '92', '93', '94'))
OR (e.code = 46 AND d.splitdata IN ('22', '18'))
OR (e.code = 3 AND d.splitdata IN ('3', '28'))

How to remove redundant conditions in **IN** query Sql

2 Answers2

How to remove redundant conditions in IN query Sql