CASE in WHERE clause?

I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:

select name, address, city etc
from customer
where if users want to see customers of a certain type then type = @.myInParameterForType
and if users want to see customers from a certain area then area = @.myInParameterForArea

is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!Yes, you may use CASE in where|||one easy (but not very performant) way is to build your "where" clause in your application (ie:web form ) and send it to your sp:
------------------------
CREATE PROCEDURE mySP

(
@.WhereClause varchar(4000)
)

AS
Declare @.SQL varchar(6000)

begin

set @.SQL = 'SELECT field1, field2 ... from myTable where ' + @.WhereClause + ' ORDER BY someID'

end

EXEC (@.SQL)

RETURN

GO
------------------------

when you have many optional parameter it is very conveniant

Cascading Parameter issue with RS2005... It worked in RS2000

Our company is attempting to convert several RS2000 reports to RS2005. We have 4 default parameters on every report.

1) Date Range (dropdown) – populated via Non-Queried Available values (Ex. Today, Week to Date, Month to Date, Fiscal Period)
2) Fiscal Period (cascading dropdown) – populated via Query Available values (if Date Range = ‘Fiscal Period’, select the last 24 fiscal months, else select Date Range value)
3) Start Date (cascading textbox) – populated via Query Default values (depending on Fiscal Period value calculate Start Date)
4) End Date (cascading textbox) – populated via Query Default values (depending on Fiscal Period value calculate End Date)

The user starts by selecting a Date Range which auto populates the Fiscal Period the auto populates the 2 Date textboxes. This works great in RS2000; however it’s not 100% in RS2005. Here’s what happens:

If a user selects ‘Today’ or ‘Month to Date’ in Date Range, everything works fine. At this point there is only 1 value in Fiscal Period dropdown. If the user selects ‘Fiscal Period’ in the Date Range, the Fiscal Period is populated with 24 values (1 for the last 24 fiscal months) and is set to the top value (June – 2006). The 2 Date textboxes are also populated correctly.

The problem is when the user changes the value in the Fiscal Period dropdown. Let's say the pick 'Mar - 2004'. The 2 Date fields are not refreshed or updated. If the user changes the selection back in the first dropdown (Date Range), everything is refreshed and updated appropriately. I don’t under stand why the first dropdown will refresh all the dependent parameters but the second dropdown won’t refresh the 2 Date fields below it?

Any input would be very helpful. I’ve tested this on 3 different development boxes and 2 different RS2005 servers. All tests had the same results.

Thanks,
Nick

Any ideas why this thing works in RS2000, but not in RS2005? Any input would be valued. Thanks,

Nick

Capacity Planning

Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
PeterThis information is in SQL Server 2000 Books Online. Look up the chapter:
"Estimating the size of a database"
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:f97601c3f222$4697f700$a001280a@.phx.gbl...
Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
Peter|||Thank you
Peter
>--Original Message--
>This information is in SQL Server 2000 Books Online. Look
up the chapter:
>"Estimating the size of a database"
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f97601c3f222$4697f700$a001280a@.phx.gbl...
>Dear All
>My company has asked me to come up with the amount of
>space a new database will use based upon X number of
>records in tables.
>Is there some sort of recognised matrix I can follow, or
>will I have to wing it based upon my own interpretation of
>the tables and relationships ?
>Thanks
>Peter
>
>.
>

Capacity Planning

Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
PeterThis information is in SQL Server 2000 Books Online. Look up the chapter:
"Estimating the size of a database"
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:f97601c3f222$4697f700$a001280a@.phx.gbl...
Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
Peter|||Thank you
Peter

>--Original Message--
>This information is in SQL Server 2000 Books Online. Look
up the chapter:
>"Estimating the size of a database"
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f97601c3f222$4697f700$a001280a@.phx.gbl...
>Dear All
>My company has asked me to come up with the amount of
>space a new database will use based upon X number of
>records in tables.
>Is there some sort of recognised matrix I can follow, or
>will I have to wing it based upon my own interpretation of
>the tables and relationships ?
>Thanks
>Peter
>
>.
>