Best way to search for all records (Using a Case Statement in a SP)

I have a form with a dropdown or combo box, the user can select <All>
or pick a user name. If they pick a user name my where clause works
fine, buts what's the best way to write "Select All" if they choose
the <All
This is what I have so far, but I don't think I should be using the
LIKE operator.

WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
@.myqarep END
and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
@.myoffice END

thanks for your help!!On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:

>I have a form with a dropdown or combo box, the user can select <All>
>or pick a user name. If they pick a user name my where clause works
>fine, buts what's the best way to write "Select All" if they choose
>the <All>
>This is what I have so far, but I don't think I should be using the
>LIKE operator.
>WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
>@.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
>thanks for your help!!

Hi Paul,

You could use this:

WHERE tblCase.qarep = CASE @.myqrep
WHEN '<All>' THEN tblCase.qarep
ELSE @.myqrep
END
AND tblOffice.officecode = CASE @.myoffice
WHEN -1 THEN tblOffice.officecode
ELSE @.myoffice
END

An alternative that's a bit more work to create but that will probably
perform better is to write different versions of the query; use IF to
select which search arguments are set to All and which are set to a value
and then execute the correct version of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||PaulMac (paulmac106@.hotmail.com) writes:
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END

Since it's a combo, LIKE appears to be a bit of overkill. The normal
procedure is to pass NULL when you want all:

WHERE (qarep = @.myqarep OR @.myqarep IS NULL)
AND (officecode = @.myoffice OR @.myoffice IS NULL)

But of course this works too:

WHERE (qarep = @.myqarep OR @.myqarep = '<All>')
AND (officecode = @.myoffice OR @.myoffice = -1)

At least as long as you don't localize the string...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I will not suggest putting a CASE command in the WHERE clause, you may
just want to use "if" control statement to separate two code blocks
which will be more efficient at database level.

IF Boolean_expression
{ sql_statement | statement_block }
[
ELSE
{ sql_statement | statement_block } ]

paulmac106@.hotmail.com (PaulMac) wrote in message news:<1ee8a467.0409130947.4f608eba@.posting.google.com>...
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
> thanks for your help!!|||Hi Hugo

That worked perfectly...Thank You!!

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<3e6ck0dsnb7m8pvq95ps7ceu2nu0lj353t@.4ax.com>...
> On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:
> >I have a form with a dropdown or combo box, the user can select <All>
> >or pick a user name. If they pick a user name my where clause works
> >fine, buts what's the best way to write "Select All" if they choose
> >the <All>
> >This is what I have so far, but I don't think I should be using the
> >LIKE operator.
> >WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> >@.myqarep END
> > and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> > @.myoffice END
> >thanks for your help!!
> Hi Paul,
> You could use this:
> WHERE tblCase.qarep = CASE @.myqrep
> WHEN '<All>' THEN tblCase.qarep
> ELSE @.myqrep
> END
> AND tblOffice.officecode = CASE @.myoffice
> WHEN -1 THEN tblOffice.officecode
> ELSE @.myoffice
> END
> An alternative that's a bit more work to create but that will probably
> perform better is to write different versions of the query; use IF to
> select which search arguments are set to All and which are set to a value
> and then execute the correct version of the query.
> Best, Hugo