How to use a null parameter in the where clause

• nadarajan_v

  SSC-Addicted

  Points: 406

  More actions

  March 26, 2011 at 6:19 pm

  #389145

  Hi,

  I have a stored proc to which I will be passing a parameter, which is of type Varchar(100). This parameter will be used in the where clause to filter the select statement. While some times a value will be passed, in other times a null will be passed. Whenever null was passed, the query should return all the records - essentially ignoring the filter, and whenever a value is passed, it should send only those records filtered by the parameter.

  Create Procedure

  {

  @LastName Varchar(100)

  }

  as

  select * from TBS_Names where lastname = @LastName

  go

  The above sp is a simplified one, and the actual query is pretty complex. I don't want to have a big if statement and two separate select statements, one when the parameter is null and another when it is not null. I believe it could be done elegantly within a single select statement using case-when statement. I tried for a while but couldn't achieve the desired result. Any help is greatly appreciated.

  Thanks

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

  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