T-SQL Query When String variable is NULL in the WHERE Clause - J. M. De Moor

SELECT    Field1, Field2, Field3, Field4, Field5
FROM   MyTable
WHERE  (Field2 IS NULL AND @myvar IS NULL)
OR (Field2 = @myvar)

T-SQL Query When String variable is NULL in the WHERE Clause - Stephany Young

Or:

select
Field1,Field2,Field3,Field4,Field5
from
MyTable
where isnull(Field2,'') = isnull(@myvar,'')

T-SQL Query When String variable is NULL in the WHERE Clause - K

Using the ISNULL function will cause a table scan whereas the first
suggestion at least has a shot at using an index if one exists. May not
matter if it's a small amount of data, but something to keep in mind.

T-SQL Query When String variable is NULL in the WHERE Clause - John Smith

Hello,

I'm having a hard time trying to create this query in the query
Analyzer.  This query will be part of a stored procedure and it will
contain a string variable.  I am supposed to get records from my table
filtered by the variable in question.

My problem is that sometimes the string variable will have to be null
and I don't know how to word the query so it uses the null value in
the where clause.

I believe my problems lies in the equal sign (=)  from the where
clause.  I can't make a field = null.   I think it should be where
Field2 Is NULL.


This is a rough example of the query.

DECLARE @myvar varchar(22)

--the following steps are only to test for Null values
SET @myvar = '' --setting the variable to an empty string so later on
could be set to Null
SET @myvar = CASE WHEN @myvar = '' THEN NULL ELSE @myvar  --converting
it to Null

-- this works if the variable is not null
SELECT    Field1, Field2, Field3, Field4, Field5
FROM   MyTable
WHERE  Field2 = @myvar


-- I'm aware the proper way to get null values would be
SELECT    Field1, Field2, Field3, Field4, Field5
FROM   MyTable
WHERE  Field2 IS NULL

How do I combine these 2 queries into one?

T-SQL Query When String variable is NULL in the WHERE Clause - John Smith

Thanks a lot!