Allowing a Stored Procedure Argument to be NULL

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

I've written a stored procedure and would like to filter the results
returned based on the argument. It works so far, but I'd also like to allow
this argument to be null.

I know I can rewrite my procedure using ISNULL(). But then it won't work for
the non-null arguments.

Does anyone know a trick to make this work? Thanks.
 
Search google and you should find plenty of articles discussing various
approaches to this requirement. Here's a sample Where clause that works for
both null and non-null arguments:

Where (@city Is Null Or @city = Address.city)
 
Paul,
Search google and you should find plenty of articles discussing various
approaches to this requirement. Here's a sample Where clause that works
for both null and non-null arguments:
Click to expand...

Yeah, I searched a bit but wasn't coming up with anything useful so far.
Where (@city Is Null Or @city = Address.city)
Click to expand...

Unfortunately, that won't cut it. If @City is not NULL, then records with
City IS NULL should not be returned. It's like I want to say WHERE
City=@City, but, if @City is NULL, then it doesn't work like I'd expect it
to.

Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com
 
thats because in set theory the empty set (null) does not equal another
empty set. you can use the coalesce function:


where coalesce(@city,address.city,'') = coalesce(address.city,'')

-- bruce (sqlwork.com)
 
just take it a step further then

WHERE @City is null OR ((@City is not null) AND (@City = Address.city))

That way it's either null, or it's not null and it matches the city field.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - Expression

Jonathan Wood said:
Paul,
Search google and you should find plenty of articles discussing various
approaches to this requirement. Here's a sample Where clause that works
for both null and non-null arguments:
Click to expand...

Yeah, I searched a bit but wasn't coming up with anything useful so far.
Where (@city Is Null Or @city = Address.city)
Click to expand...

Unfortunately, that won't cut it. If @City is not NULL, then records with
City IS NULL should not be returned. It's like I want to say WHERE
City=@City, but, if @City is NULL, then it doesn't work like I'd expect it
to.

Thanks.
Click to expand...
 
If @city is not null, then a null Address.city will not satisfy either part
of the Where clause. @city=Address.city will be false if Address.city is
null. Try it and you'll see it works. I guess I should add that I'm using
SQL Server 2005 and it works correctly there. I know it has worked in
earlier SQL Server versions, and should work in any db, but I haven't tested
it elsewhere.

Bruce suggested Where coalesce(@city, address.city, "") =
coalesce(address.city, '') which would also work. The disadvantage to this
format is it cannot use an index on Address.city if one exists, since both
sides of the expression are functions. Even without an index I would expect
the first form to run a little faster since it doesn't have to evaluate any
functions.
 
You can branch the logic.

IF (@city IS NULL)
BEGIN
--NULL version
END
ELSE
BEGIN
--Non NULL version
END

You will end up with duplicate code in most instances, but it works.

If your logic is very complex, you can use branches to set up records in a
temp table that fit your desires. You start with one that gives the
narrowest set of IDs and then prune out numbers with other branches. Then
actual query ends up something like:

SELECT * FROM Table1
WHERE ID IN (SELECT ID FROM #Temp)

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

*************************************************
| Think outside the box!
|
*************************************************
 
bruce,
thats because in set theory the empty set (null) does not equal another
empty set. you can use the coalesce function:
Click to expand...

Yeah, I kind of figured that out. Just trying to find an easy workaround.
where coalesce(@city,address.city,'') = coalesce(address.city,'')
Click to expand...

I'm just trying to understand this. Can you explain why it wouldn't just be:

WHERE COALESCE(@city,'') = coalesce(address.city,'')

To the extent I understand it (not that much), it seems like your version
might produce a false match if @city is null, but address.city is not.

Thanks.
 
Well, now that's starting to get a little convoluted. But, unless I'm
missing something, it would not detect the case where @City is NULL and
Address.City is not NULL. In this case, there should be no match. It would
probably need to be even a bit more convoluted.
 
Back
Top