Allowing a Stored Procedure Argument to be NULL
Jonathan Wood
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
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.com> wrote in message
news:ebeP$clqIH...@TK2MSFTNGP03.phx.gbl...
Jonathan Wood
> 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.
bruce barker
empty set. you can use the coalesce function:
where coalesce(@city,address.city,'') = coalesce(address.city,'')
-- bruce (sqlwork.com)
Mark Fitzpatrick
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.com> wrote in message
news:e9xELJnq...@TK2MSFTNGP02.phx.gbl...
Paul Shapiro
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.com> wrote in message
news:e9xELJnq...@TK2MSFTNGP02.phx.gbl...
Cowboy (Gregory A. Beamer)
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.com> wrote in message
news:ebeP$clqIH...@TK2MSFTNGP03.phx.gbl...
Mark Rae [MVP]
news:eZdWk$oqIHA...@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
> 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.
Jonathan Wood
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" <mark...@fitzme.com> wrote in message
news:eZdWk$oqIHA...@TK2MSFTNGP05.phx.gbl...