Embeded case in where clause - causing problems.

Hi.
I have a sp that allows the user to search based on a variable set of parame
ters for example just a home phone or just a buss. phone however when search
ing by last name the user also has to supply the ZIP
optionally he can filter that search by first name or address. I am trying t
o accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does not
work - I am sure that are fields with those values in the db
(note: everying worked as expected before I added the embeded cases (and wil
dcard stuff))
the following is the SP - (sorry for the long snippet)
ALTER procedure GetDupCheckResults
(
@.HomePhone varchar(50),
@.BussPhone varchar(50),
@.Email varchar(50),
@.LastName varchar(50),
@.FirstName varchar(50),
@.Address varchar(50),
@.FirmZip varchar(50),
@.PersonZip varchar(50),
@.FirmName varchar(50)
)
as
begin
SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype +
' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
FROM
(
select v_nmf_naf_npf_linked.* from
v_nmf_naf_npf_linked inner join
(
Select distinct npfseq from
(
select
npfseq,'HomePhone' FoundSource
from
v_nmf_naf_npf_Linked
where
npfArea + '-' + NPFPhone = @.HomePhone
and
naftype = 'HOME'
and
(
@.HomePhone <> ''
and
@.HomePhone <> '%%'
)
union
select
npfseq,'BussPhone'
from
v_nmf_naf_npf_Linked
where
npfArea+'-'+NPFPhone = @.BussPhone
and
naftype = 'BF'
and
@.BussPhone <> ''
union
select
npfseq,'NameAddress'
from
V_nmf_naf_npf_Linked
where
@.PersonZIP like nafzip
and
@.LastName like nmflast
and
-- optionally allow filter on address and last name
1 =
Case @.Address
When '' then 1
When '%%' then 1 -- when wild cards are in use
Else
(
Case
when @.Address like nafAdd1 then 1
when @.Address like nafAdd2 then 1
else
0
end
)
end
and
1 =
Case @.LastName
When '' then 1
When '%%' then 1
Else
(
Case
when @.Lastname like nmfLast then 1
else 0
end
)
end
and not
(
(@.LastName = '' or @.PersonZip = '')
or
(@.LastName = '%%' or @.PersonZip = '%%')
)
union
select
npfseq,'Company'
from
V_nmf_naf_npf_Linked
where
nafZip like @.FirmZip
and
nafCompany like @.FirmName
and not @.FirmName = ''
and not @.FirmZip = ''
and not @.FirmName = '%%'
and not @.FirmZip = '%%'
)Temptab
)
unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
) dd
end
thank you for slogging thru it.You are sure you have people with the last name '%s%' and zip '%1%'?
What country is this?
Most likely you don't have such data in your database, but that is exactly
what this code will try to find, since your condition (on last name, for
example) is
@.Lastname like nmfLast
While this stored procedure looks far more complicated than it needs to
be, my guess is that you want
nmfLast LIKE @.Lastname
instead of the other way around.
Steve Kass
Drew University
Madler wrote:

>Hi.
>I have a sp that allows the user to search based on a variable set of param
eters for example just a home phone or just a buss. phone however when searc
hing by last name the user also has to supply the ZIP
>optionally he can filter that search by first name or address. I am trying
to accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
>For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does no
t work - I am sure that are fields with those values in the db
>(note: everying worked as expected before I added the embeded cases (and wi
ldcard stuff))
>the following is the SP - (sorry for the long snippet)
>ALTER procedure GetDupCheckResults
>(
> @.HomePhone varchar(50),
> @.BussPhone varchar(50),
> @.Email varchar(50),
> @.LastName varchar(50),
> @.FirstName varchar(50),
> @.Address varchar(50),
> @.FirmZip varchar(50),
> @.PersonZip varchar(50),
> @.FirmName varchar(50)
> )
>as
>begin
>SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype
+ ' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
>FROM
>(
>select v_nmf_naf_npf_linked.* from
> v_nmf_naf_npf_linked inner join
> (
> Select distinct npfseq from
> (
> select
> npfseq,'HomePhone' FoundSource
> from
> v_nmf_naf_npf_Linked
> where
> npfArea + '-' + NPFPhone = @.HomePhone
> and
> naftype = 'HOME'
> and
> (
> @.HomePhone <> ''
> and
> @.HomePhone <> '%%'
> )
> union
> select
> npfseq,'BussPhone'
> from
> v_nmf_naf_npf_Linked
> where
> npfArea+'-'+NPFPhone = @.BussPhone
> and
> naftype = 'BF'
> and
> @.BussPhone <> ''
> union
> select
> npfseq,'NameAddress'
> from
> V_nmf_naf_npf_Linked
> where
> @.PersonZIP like nafzip
> and
> @.LastName like nmflast
> and
> -- optionally allow filter on address and last name
> 1 =
> Case @.Address
> When '' then 1
> When '%%' then 1 -- when wild cards are in use
> Else
> (
> Case
> when @.Address like nafAdd1 then 1
> when @.Address like nafAdd2 then 1
> else
> 0
> end
> )
> end
> and
> 1 =
> Case @.LastName
> When '' then 1
> When '%%' then 1
> Else
> (
> Case
> when @.Lastname like nmfLast then 1
> else 0
> end
> )
> end
> and not
> (
> (@.LastName = '' or @.PersonZip = '')
> or
> (@.LastName = '%%' or @.PersonZip = '%%')
> )
>
> union
> select
> npfseq,'Company'
> from
> V_nmf_naf_npf_Linked
> where
> nafZip like @.FirmZip
> and
> nafCompany like @.FirmName
> and not @.FirmName = ''
> and not @.FirmZip = ''
> and not @.FirmName = '%%'
> and not @.FirmZip = '%%'
>
> )Temptab
> )
> unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
> ) dd
>end
>
>thank you for slogging thru it.
>
>