Better Way to do that Like ''%'' + + ''%'' or is Null) i

• Frances L

  SSCarpal Tunnel

  Points: 4727

  More actions

  August 28, 2006 at 12:00 pm

  #114107

  CREATE PROCEDURE [dbo].[getDueList]

  (

  @unit VARCHAR(5 ),

  @cc varchar(11),

  @quizname VARCHAR( 25 )

  )

  AS

  select unit, JobCCNO, [FULL NAME], SSN, EMPLNO ,[Unit Desc] , Department from dbo.occhemp

       select * from dbo.OcchEmp E

        

       where ( (E.Unit like '%'+@unit + '%' or @unit is null )

        and ( E.JobCCNo like '%'+ @cc +'%' or @cc is Null ) and (

   not exists (

     select *

           from dbo.[WinQuizLog] L

       where (L.[Employee Name]= E.[Full Name] and L.DOB = E.DOB and L.[Quiz Name] like '%' +@quizname +'%' or @quizname is null ) )

  )

  )

  GO

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  August 28, 2006 at 2:56 pm

  #657204

  You could try adding an ISNULL, but I am not sure that will increase your processing time. 

  WHERE( ISNULL( E.Unit, @unit) LIKE '%' + @unit + '%') ...

  I wasn't born stupid - I had to study.

• Paul E

  SSC Enthusiast

  Points: 134

  More actions

  August 29, 2006 at 11:57 am

  #657325

  If you mean "better way" in the sense of easier to read/maintain, then I would just have a line before the select statement that said

  SET @var = '%' + Coalesce(@var,'') + '%'

  and do all subsequent comparisons ala

  WHERE Name LIKE @var

  If you're looking for advice on dynamic search criteria generally, try http://www.sommarskog.se/dyn-search.html