Testing for whitespace in SQL Server

ltrim(rtrim(isNull(@value,''))) = ''

To compare with NULL, use the IS NULL keyword.

--Generic example:
SELECT *
FROM MY_TABLE
WHERE SOME_FIELD IS NULL;

--Instead of    

SELECT *
FROM MY_TABLE
WHERE SOME_FIELD = NULL;

if length(@value) = 0 or @value is null