Dealing with a null parameter in SP

[Lauren Quantrell]

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.Contac tID, ContactType,
CASE WHEN Contacts.Contac tType = 0
THEN Contacts.Compan yName
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(CompanyNam e,1) END)

Any help is appreciated,
LQ

[Erland Sommarskog]

Re: Dealing with a null parameter in SP

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> 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(CompanyNam e,1) END)
>[/color]

What about:

@Alpha = CASE ContactType
WHEN 0 THEN Left(LastName, 1)
WHEN 1 THEN Left(CompanuNam e, 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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Lauren Quantrell]

Re: Dealing with a null parameter in SP

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

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns95AB21 6F37D4Yazorman@ 127.0.0.1>...[color=blue]
> Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=green]
> > 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(CompanyNam e,1) END)
> >[/color]
>
> What about:
>
> @Alpha = CASE ContactType
> WHEN 0 THEN Left(LastName, 1)
> WHEN 1 THEN Left(CompanuNam e, 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.[/color]