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.

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... (AT) hotmail (DOT) com wrote:

On Jul 5, 4:01 pm, Jason Lepack <jlep... (AT) gmail (DOT) com> wrote:
OK, thank you! I was missing the "OR LastName is null)" The proc
works great now.

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 (AT) gmail (DOT) com>
wrote:

On Jul 5, 4:32 pm, Roy Harvey <roy_har... (AT) snet (DOT) net> wrote:
Yes, you're right. Thanks for the tip.