Case Is Not Working Properly

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

Solution 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 = (CASEWHEN@MyParamISNULLTHEN M.EmpID ELSE@MyParamEND)

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

Solution 2:

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

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

Solution 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 @MyParamISNULLTHENSELECT*FROM@MyTableELSESELECT*FROM@MyTableWHERE EmpID =@MyParam

Solution 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 isNULLand@MyParamisNULL);

Solution 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