CASE is not working properly

Question

I have following two queries. The first query is straight forward and is working as epecte when @MyParam is null. But the second query is not returning the same result. What is the mistake in the second query.

Note: I have been asked to use the second approach since the client asked to get rid of “@MyParam IS NULL” check (used in query 1)

QUERY

DECLARE @MyParam INT

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 = @MyParam OR @MyParam IS NULL

-- Query 2 (Rewrite for performance)
SELECT * 
FROM @MyTable M
WHERE M.EmpID = (CASE @MyParam WHEN NULL THEN M.EmpID ELSE @MyParam END)

Answer 1

You could write

SELECT * 
FROM @MyTable M
WHERE M.EmpID = IsNULL(@MyParam,M.EmpID)

Or if you need to use casefor some other reason

WHERE M.EmpID = (CASE WHEN @MyParam IS NULL THEN M.EmpID ELSE @MyParam END)

Case is working properly. Select is not broken. http://pragmatictips.com/26

Answer 2

You should use IS NOT NULL. NEVER compare NULL with anything

NULL=NULL - > false
NULL <> NULL -> false

Answer 3

Why not just use

SELECT * 
FROM @MyTable M
WHERE M.EmpID = ISNULL(@MyParam, M.EmpID)

or if it's performance you seek, checking for @MyParam with an IF statement

IF @MyParam IS NULL THEN
  SELECT * FROM @MyTable
ELSE
  SELECT * FROM @MyTable WHERE EmpID = @MyParam

Answer 4

I would use coalesce() for this purpose. It is the shorted, ANSI standard method:

SELECT m.* 
FROM @MyTable m
WHERE m.EmpID = coalesce(@MyParam, m.EmpID);

Note that this logic is subtly different from the first method, in the case the m.EmpId is NULL. The equivalent logic would be:

SELECT m.* 
FROM @MyTable m
WHERE m.EmpID = coalesce(@MyParam, m.EmpID) or (m.EmpId is NULL and @MyParam is NULL);

Answer 5

The syntax is CASE WHEN @MyParam IS NULL THEN M.EmpID ELSE @MyParam END

please see similar answer here:

https://stackoverflow.com/a/3237670/11436