SEO

#1 (**permalink**) 02-29-2008, 04:56 AM

I have a stored procedure as a recordsource from a contacts table. In
this example, users can enter parameters to limit contacts by first
letter of last name or company name or keywords:

Example:

@myName nvarchar(30) = null,
@Alpha char(1) = null

SELECT
Contacts.ContactID, ContactType,
CASE WHEN Contacts.ContactType = 0
THEN Contacts.CompanyName
ELSE
isNull(Contacts.LastName,'?') + ', ' +
isNull(Contacts.FirstName,'?')
END
AS CNAME
FROM
Contacts
WHERE
(Keywords Like '%' + @myKeyword + '%' OR @myKeyword is Null)

So far, so good, but...

The problem is I want to also give the user the option of filtering
alphabetically by first letter. I can't figure out how to deal with
nulls in this example (user doesn't enter anything as parameter
@Alpha):

AND
(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) END
OR
@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)

Any help is appreciated,
LQ

#2 (**permalink**) 02-29-2008, 04:56 AM

Lauren Quantrell (laurenquantrell@hotmail.com) writes:
> The problem is I want to also give the user the option of filtering
> alphabetically by first letter. I can't figure out how to deal with
> nulls in this example (user doesn't enter anything as parameter
> @Alpha):
>
> AND
> (@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) END
> OR
> @Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)
>

What about:

@Alpha = CASE ContactType
WHEN 0 THEN Left(LastName, 1)
WHEN 1 THEN Left(CompanuName, 1)
END
OR @Alpha Is NULL

Beware that if you are hoping for any sort of index usage, this is not
going to fly at all.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#3 (**permalink**) 02-29-2008, 05:00 AM

Erland,
Thank you. I didn't think that one through enough!
lq

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns95AB216F37D4Yazorman@127.0.0.1>...
> Lauren Quantrell (laurenquantrell@hotmail.com) writes:
> > The problem is I want to also give the user the option of filtering
> > alphabetically by first letter. I can't figure out how to deal with
> > nulls in this example (user doesn't enter anything as parameter
> > @Alpha):
> >
> > AND
> > (@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) END
> > OR
> > @Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)
> >
>
> What about:
>
> @Alpha = CASE ContactType
> WHEN 0 THEN Left(LastName, 1)
> WHEN 1 THEN Left(CompanuName, 1)
> END
> OR @Alpha Is NULL
>
> Beware that if you are hoping for any sort of index usage, this is not
> going to fly at all.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Dealing with a null parameter in SP