t-sql 2012 pass in null values

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  September 20, 2014 at 12:07 pm

  #311075

  The following t-sql 2012 works fine in sql management studio. However when I place it in a .net 2010 web form application, I am told the sql does not work when the parameter values are null. Thus can you tell me what I can change in the sql below that will accept null as 3 possible input values?

  SELECT i.[lastName]

  ,i.[firstName]

  ,i.[middleName]

  ,i.[suffix]

  ,a.[userid]

  ,a.schoolnum

  ,a.spa

  ,a.mrref

  from [OPS].[dbo].[Identity] i

  inner join [OPS].[dbo].[Person] p on i.identityID = p.currentIdentityID

  inner join [OPS].[dbo].[UserAccount] u on u.personID = p.personID

  inner join [CampusOps].[dbo].[AtnLtrUsers] a on a.userid =u.username

  where (i.[lastName] like '%' + @lname + '%' or i.[firstName] like'%' +

  @fname + '%'

  or i.[middleName] like'%' + @mname + '%')

  or (@fname is null and @lname is null and @mname is null)

  order by i.[lastName], i.[firstName], i.[middleName]"

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  September 20, 2014 at 1:27 pm

  #1746893

  Quick thought, can you change this to a stored procedure and set default values for the parameters?

  😎