 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / April 2008Allowing a Stored Procedure Argument to be NULL
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.  SignatureJonathan Wood SoftCircuits Programming http://www.softcircuits.com 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) > 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 [quoted text clipped - 4 lines] > > Does anyone know a trick to make this work? Thanks. 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. SignatureJonathan Wood SoftCircuits Programming http://www.softcircuits.com >> 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 [quoted text clipped - 4 lines] >> >> Does anyone know a trick to make this work? Thanks. 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) > Paul, > [quoted text clipped - 12 lines] > > Thanks. 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. SignatureJonathan Wood SoftCircuits Programming http://www.softcircuits.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 > Paul, > [quoted text clipped - 21 lines] >>> >>> Does anyone know a trick to make this work? Thanks. > 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. SignatureMark Rae ASP.NET MVP http://www.markrae.net 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. SignatureJonathan Wood SoftCircuits Programming http://www.softcircuits.com > just take it a step further then > [quoted text clipped - 31 lines] >>>> >>>> Does anyone know a trick to make this work? Thanks. 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. > Paul, > [quoted text clipped - 21 lines] >>> >>> Does anyone know a trick to make this work? Thanks. 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) SignatureGregory 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! ************************************************* > 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 [quoted text clipped - 4 lines] > > Does anyone know a trick to make this work? Thanks.
Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.