I believe that will return Tom NULL when they ask for Tom Smith. If
they intend to reject a NULL last name when a last name is give then
the WHERE clause would have to change slightly.

WHERE
FirstName LIKE '%' + ISNULL(@FirstName, '') + '%'
AND
(
LastName LIKE '%' + ISNULL(@LastName, '') + '%'
OR (LastName is null AND @LastName IS NULL)
)

Roy Harvey
Beacon Falls, CT

On Thu, 05 Jul 2007 14:01:15 -0700, Jason Lepack <jlepack@gmail.com>
wrote:

>SELECT
> FirstName,
> LastName,
> BusinessName,
> MailingAddress1
>FROM
> tblAddresses
>WHERE
> FirstName LIKE '%' + ISNULL(@FirstName, '') + '%'
> AND
> (
> LastName LIKE '%' + ISNULL(@LastName, '') + '%'
> OR LastName is null
> )
>
>
>
>
>
>On Jul 5, 4:56 pm, manning_n...@hotmail.com wrote:
>> I've got the following SELECT statement in a stored procedure:
>>
>> SELECT FirstName, LastName, BusinessName, MailingAddress1
>> FROM tblAddresses
>> WHERE FirstName LIKE '%' + ISNULL(@FirstName, '') + '%' AND
>> LastName LIKE '%' + ISNULL(@LastName, '') + '%'
>>
>> If I input "tom" for @FirstName then it returns rows with "tom"
>> somewhere in the first name but that row must have a last name too in
>> order to be returned. How do I change this to return both types of
>> rows: one with "tom" as a firstname and with a lastname and one with
>> "tom" as first name but no last name?
>>
>> Thanks for any help or advice.
>