Optional parameter in WHERE clause

Question

I have a query something like this:

declare @LastName varchar(50), @firstletter varchar(50)

set @LastName ='%Jones%'

select * from tblSample where Last_Name like @LastName

This query returns all records from tblSample where the Last_Name field is LIKE the value in the variable @LastName

Sometimes the results can contain too many records, such as when the last name is Jones, or Smith, etc. In those cases I want to enter the first initial of the first name. However, So if I had a variable like:

declare @name varchar(50), @firstletter varchar(50)

set @FirstInitial ='T%'

select * from tblSample where Last_Name like @LastName AND First_Name Like @FirstInitial

How would I condition the where clause to only look for the value in First_Name if it is not ''.

tod

I don't know what to do, so I didn't try anything yet.

Answer 1

Assuming no value would be supplied for @FirstInitial unless required and therefore @FirstInital would be NULL then the following will work

SELECT * 
    FROM tblSample
    WHERE Last_Name LIKE @LastName
      AND (First_Name LIKE @FirstInitial OR @FirstInitial  IS NULL)