Reputation: 46222

sql - where clause NULL

I have the following in my where clause

    WHERE st.State =  @St

In this case @St is NULL. If st.State is also NULL this will not return TRUE. How do I do my where clause in this case?

I was thinking of

    WHERE st.State =  @St AND @St IS NULL

but not sure if that will buy me much.

Upvotes: 2

Answers (7)

Reputation: 659

  select column1 from Table1
  where (@param is null and column2 is null)
  or (column2 = @param)

Upvotes: 0

Reputation: 866

check this. this will work.

ISNULL(st.State,'') = ISNULL(@St,'')

Upvotes: 0

Reputation: 156

Not sure what system you're working on, but I'm fond of IsNull on SQL Server systems:

WHERE (IsNull(st.State,'none')=IsNull(@St,'none')

Upvotes: 2

Reputation: 10582

You might need to do different things on different databases, but one general approach is

WHERE st.State = @St OR (@St is NULL and st.State is NULL)

Upvotes: 2

Reputation: 4520

Try

 WHERE (st.State = @St or (st.State is NULL and @St is NULL))

Upvotes: 2

Reputation: 2018

You could use the following:

WHERE ( @St IS NULL AND st.State IS NULL ) OR ( @St = st.State)

Upvotes: 4

Reputation: 37388

WHERE st.State = @St OR (@St IS NULL AND st.State IS NULL)

Upvotes: 4