How to use a null parameter in the where clause

• SunitaBeck

  SSC Veteran

  Points: 277

  More actions

  March 26, 2011 at 8:27 pm

  #1304015

  Create Procedure

  {

  @LastName Varchar(100) = null

  }

  as

  select * from TBS_Names where (@LastName is null or lastname = @LastName)

  go

  But, before you go with this approach, also please read this blog about performance issues that may result from plan caching: http://blogs.msdn.com/b/bartd/archive/2009/05/03/sometimes-the-simplest-solution-isn-t-the-best-solution-the-all-in-one-search-query.aspx

• nadarajan_v

  SSC-Addicted

  Points: 406

  More actions

  March 26, 2011 at 11:48 pm

  #1304022

  Hi SunitaBeck,

  Thanks for the help. The reference you have mentioned was extremely good!

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  March 27, 2011 at 3:24 am

  #1304038

  Read through this as well: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/. sunitabeck's solution does not perform well on larger resultsets.

  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