LIKE function in SQL Server 2008 suggestion needed

Question

I have a table Customer with 2 columns Name and Surname (sql Server 2008). User wants to search by Name-Surname - or just typing Jo Bloggs. Suppose a row is filled with

CustomerId        Name    Surname
1                 Jo      Bloggs

doing

select * from customer where Name like '%Jo%'             will find the records
select * from customer where Surname like '%Bloggs%'      will find the records

But How can I make it return records for if user types both Name and Surname eg Jo Bloggs?

Answer 1

You could also create a computed column and search on that:

ALTER TABLE dbo.customer
   ADD FullName as Name + ' ' + Surname PERSISTED

Now you'd have an extra column FullName in your customer table, which is always up to date, and you can search for:

SELECT (list of fields) FROM dbo.customer 
  WHERE Fullname = 'Jo Bloggs'

and since it's a persisted, computed column, you can even put an index on it to make exact searches faster

Answer 2

This should work whether user enters First Name, Surname or both.

SELECT *
FROM customer cus
WHERE ((cus.Name like @FirstName) OR (@FirstName IS NULL)) AND
      ((cus.Surname like @Surname) OR (@Surname IS NULL))

I used two variables because entering input values into an SQL string is strongly discouraged, as it exposes to SQL Injection (other than being slower).