4

I am having some difficulty returning all the results I would expect when leaving an optional sql parameter blank on a column that contain null values.

Imagine you have a table with the following (referredby is optional and can therefore be NULL): 

Customertable
ID    CustomerName  ReferredBy
1      Aaron         Joe
2      Peter         NULL
3      Steven        Joe

Suppose I want to query with an optional SQL parameter for the referredby field like such:

declare @referredby as varchar(15)

select id, customername
from customertable<br>
where referredby = isnull(@referredby, referredby)

If I leave the parameter null, this would only return:
1 Aaron
3 Steven

How can I use an optional parameter to return all 3 results?

John Woo
  • 258,903
  • 69
  • 498
  • 492
AKM
  • 43
  • 1
  • 1
  • 4

2 Answers2

6

Try This:

select id, customername
from customertable
where (referredby = @referredby OR @referredby is null)

For reasons explained in this post, comparing null = null in sql server returns false (or unknown). As does null != null.

If you really like your syntax I believe you could make it work by setting ansi_Nulls to off: set ansi_nulls off

Community
  • 1
  • 1
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Ah, from what I had read, I thought that the syntax you used, and the isnull()... syntax were interchangeable – AKM Jun 18 '12 at 23:50
  • No, it's important to know that `IS NULL` and `ISNULL()` do very different things in SQL Server. It can be confusing if you aren't familiar with the distinction between the two. – Abe Miessler Jun 18 '12 at 23:52
2

Add this line on your query:

SELECT ... FROM ... --other codes here
where (referredby = @referredby) OR (@referredby IS NULL)
John Woo
  • 258,903
  • 69
  • 498
  • 492