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

[PaulMac]

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

[Hugo Kornelis]

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)

[Erland Sommarskog]

PaulMac (pa********@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, es****@sommarskog.se

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

[Rajesh Kumar]

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 } ]


pa********@hotmail.com (PaulMac) wrote in message news:<1e**************************@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!!

[PaulMac]

Hi Hugo

That worked perfectly...Thank You!!

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<3e********************************@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