create table #test(id int, name varchar(100)) insert into #test values (1,'aaa') insert into #test values (2,'bbb') insert into #test values (3,'ccc') declare @search varchar(100)= 'a' --(1) select * from #test where 1=1 and ( name like isnull(@search,'') + '%') --OR @search is null --(2) select * from #test where 1=1 AND ( @search IS NOT NULL AND (name like @search + '%') OR(@search IS NULL AND 1=1))
You may want to look at Erland's article on dynamic search conditions over at http://sommarskog.se []