Using isnull in where filter

Question

Using isnull in where filter

dndaughtery

SSChampion

Points: 11290
More actions
September 23, 2014 at 6:36 am

#293523

Hey guys, I remember in the past using IsNull in a where filter of a stored proc that was used for a search form. I typically go about this in the following way. Is threre a better way to handle this?
sproc parm
@LName varchar(50) = null
@FName varchar(50) = null
Select * from LargeTable
where Lastname = ISNULL(@LName, Lastname)
Or Firstname = ISNULL(@FName, Firstname)
I do it this way to ensure that if the user doesn't enter a value for one of the parameters that it won't be used in the filter.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dndaughtery SSChampion Points: 11290 More actions · Answer 2

Actually I just noticed I would need to provide fuzzy lookup as well

ScottPletcher SSC Guru Points: 101121 More actions · Answer 3

Never use ISNULL() in a WHERE or in JOIN conditions. You can always code around it, and it causes optimizer issues. There are very few times when you can safely say "always" or "never" in SQL/db issues, but this is one of them.

Code it like this instead:

Select *

from LargeTable

where (@LName IS NULL OR Lastname = @LName )

or (@FName IS NULL OR Firstname = @FName )

For those specific cases, dynamic SQL will often run better, esp. as you get more optional params.

However, even routine SELECTs should never use ISNULL():

--wrong!

select ...

from sometable

where isnull(somecolumn, '') = ''

--correct

select ...

from sometable

where (somecolumn is null or somecolumn = '')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".