CODEWITHSUNDEEP
sql-server
LCJ
LCJ

Reputation: 22652

Rewriting query using CASE statement

I need to rewrite the following tow queries to avoid OR @MyParam IS NULL check (by using CASE Statement as mentioned in CASE is not working properly)

Is it possible to rewrite following queries in that format?

Note: The fn_Split function is available in How to pass string parameter with `IN` operator in stored procedure SQL Server 2008

DECLARE @MyParam VARCHAR(MAX)
SET @MyParam = '2,3'

DECLARE @MyTable TABLE (EmpID INT)

INSERT INTO @MyTable VALUES (1)
INSERT INTO @MyTable VALUES (2)
INSERT INTO @MyTable VALUES (3)
INSERT INTO @MyTable VALUES (4)


--Query 1
SELECT * 
FROM @MyTable M
WHERE (
        M.EmpID IN (SELECT CONVERT(int,Value) FROM fn_Split(@MyParam, ','))
        OR @MyParam IS NULL
       )


--Query 2
SELECT * 
FROM @MyTable M
WHERE (EXISTS   (
                  SELECT CONVERT(int,Value) 
                  FROM fn_Split(@MyParam, ',')
                  WHERE CONVERT(int,Value)  =  M.EmpID
                  )
                  OR @MyParam IS NULL
       )

Upvotes: 0

Views: 215

Answers (1)

podiluska
podiluska

Reputation: 51494

Use a left join

SELECT m.* 
FROM @MyTable M
    left join (SELECT CONVERT(int,Value) v FROM fn_Split(@MyParam, ',')) t 
on m.EmpID = t.v 
where v is not null or @MyParam is null

Ideally you wouldn't pass a string containing multiple values as a parameter - if this is a stored procedure, use a table valued parameter

Upvotes: 1

Related Questions