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...@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.

On Jul 5, 4:01 pm, Jason Lepack <jlep...@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.- Hide quoted text -
>
> - Show quoted text -

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@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.
>

On Jul 5, 4:32 pm, Roy Harvey <roy_har...@snet.net> wrote:
> 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 <jlep...@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.- Hide quoted text -
>
> - Show quoted text -

Yes, you're right. Thanks for the tip.