SQL Query with Optional Parameter and Possible Null Column
10,454
Solution 1
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
Solution 2
Add this line on your query:
SELECT ... FROM ... --other codes here
where (referredby = @referredby) OR (@referredby IS NULL)
Author by
AKM
Updated on June 04, 2022Comments
-
AKM almost 2 years
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 StevenHow can I use an optional parameter to return all 3 results?
-
AKM almost 12 yearsAh, from what I had read, I thought that the syntax you used, and the isnull()... syntax were interchangeable
-
Abe Miessler almost 12 yearsNo, it's important to know that
IS NULL
andISNULL()
do very different things in SQL Server. It can be confusing if you aren't familiar with the distinction between the two.