SQL Query with Optional Parameter and Possible Null Column
sql, sql-server-2005, t-sql
Solution
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`
Problem
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?