Allowing a Stored Procedure Argument to be NULL

[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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Paul Shapiro]

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)
"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Jonathan Wood]

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:

Yeah, I searched a bit but wasn't coming up with anything useful so far.

Where (@city Is Null Or @city = Address.city)

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

>
"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

>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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[bruce barker]

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)
Jonathan Wood wrote:

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:

Yeah, I searched a bit but wasn't coming up with anything useful so far.

>Where (@city Is Null Or @city = Address.city)

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.

[Mark Fitzpatrick]

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" <jw***@softcircuits.comwrote in message
news:e9**************@TK2MSFTNGP02.phx.gbl...

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:

Yeah, I searched a bit but wasn't coming up with anything useful so far.

>Where (@city Is Null Or @city = Address.city)

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

>>
"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

>>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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Paul Shapiro]

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.

"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:e9**************@TK2MSFTNGP02.phx.gbl...

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:

Yeah, I searched a bit but wasn't coming up with anything useful so far.

>Where (@city Is Null Or @city = Address.city)

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

>>
"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

>>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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Cowboy \(Gregory A. Beamer\)]

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!
|
*************************************************
"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Mark Rae [MVP]]

"Mark Fitzpatrick" <ma******@fitzme.comwrote in message
news:eZ**************@TK2MSFTNGP05.phx.gbl...

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.

Bruce has provided the correct method for this i.e. to use the COALESCE
function.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Jonathan Wood]

bruce,

thats because in set theory the empty set (null) does not equal another
empty set. you can use the coalesce function:

Yeah, I kind of figured that out. Just trying to find an easy workaround.

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

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Jonathan Wood]

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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Mark Fitzpatrick" <ma******@fitzme.comwrote in message
news:eZ**************@TK2MSFTNGP05.phx.gbl...

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" <jw***@softcircuits.comwrote in message
news:e9**************@TK2MSFTNGP02.phx.gbl...

>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:

Yeah, I searched a bit but wasn't coming up with anything useful so far.

>>Where (@city Is Null Or @city = Address.city)

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

>>>
"Jonathan Wood" <jw***@softcircuits.comwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
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.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com