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

Email Attachements

Whenever I try to create an attachment with email, the attachment is created
but it is empyu in the case of Excel or I get an open error when creating a
PDF file attachment. If insted of using email, I create a file on a server
share it works fine and I see the report. Also if I include a link on the
email that works fine also and I see the report. It's only the attachments
that aren't working. We are using Lotus Notes for our email system.It's a known bug. Apply the hotfix described in M$ KB # 872774
X
"Jeff" wrote:
> Whenever I try to create an attachment with email, the attachment is created
> but it is empyu in the case of Excel or I get an open error when creating a
> PDF file attachment. If insted of using email, I create a file on a server
> share it works fine and I see the report. Also if I include a link on the
> email that works fine also and I see the report. It's only the attachments
> that aren't working. We are using Lotus Notes for our email system.|||A hotfix has been issued for this issue:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:A8509E81-7A2C-460F-B82E-F4D06B8C4A83@.microsoft.com...
> Whenever I try to create an attachment with email, the attachment is
> created
> but it is empyu in the case of Excel or I get an open error when creating
> a
> PDF file attachment. If insted of using email, I create a file on a
> server
> share it works fine and I see the report. Also if I include a link on the
> email that works fine also and I see the report. It's only the
> attachments
> that aren't working. We are using Lotus Notes for our email system.

Efficiency in inserting Null Values into fields which allow nulls.

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanksheres some info from BOL :

Allowing Null Values
The nullability of a column determines if the rows in the table can contain a null value for that column. A null value, or NULL, is not the same as zero (0), blank, or a zero-length character string such as ""; NULL means that no entry has been made. The presence NULL usually implies that the value is either unknown or undefined. For example, a null value in the price column of the titles table of the pubs database does not mean that the book has no price; NULL means that the price is unknown or has not been set.In general, avoid permitting null values because they incur more complexity in queries and updates and because there are other column options, such as PRIMARY KEY constraints, that cannot be used with nullable columns.

If a row is inserted but no value is included for a column that allows null values, Microsoft® SQL Server? 2000 supplies the value NULL (unless a DEFAULT definition or object exists). A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what data type it is or if it has a default associated with it. The value NULL should not be placed within quotation marks because it will be interpreted as the character string 'NULL', rather than the null value.

Specifying a column as not permitting null values can help maintain data integrity by ensuring that a column in a row always contains data. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.

Note Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.
|||To be honest I'm not sure. Once you've made the decision to use NULLs (and there are lots of pros/cons to that) I doubt there is much in it. However, if you have a choice *and* you're really trying to eek out the last drop of performance then I'd avoid NULLs. Having said that I'm sure there are thousands of other places to look for better optimisations before you go here.