"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:ewJCt8LiFHA.2852@TK2MSFTNGP15.phx.gbl...
>>> To be more specific, like most applications, I have several queries that
>>> reduce the number of returned records through the use of filtering.
>>> Ideally, a user should have the option to provide a value for a filter
>>> or specify that the filter is not being used.
>
> Some of the popular approaches for such problems can be found at:
> http://www.sommarskog.se/dyn-search.html
>
> --
> Anith
>

"ChrisB" <pleasereplytogr***@thanks.com> wrote in message
news:Ooz4sxLiFHA.3568@tk2msftngp13.phx.gbl...
> Hello:
>
> I would like to add filters to some Sql Server 2000 queries and was
> wondering if someone might be able to recommend a proven design pattern.
>
> To be more specific, like most applications, I have several queries that
> reduce the number of returned records through the use of filtering.
> Ideally, a user should have the option to provide a value for a filter or
> specify that the filter is not being used.
>
> For example, Customer.GetCustomerList() might require 2 filters such as a
> "StateID" filter which is a Guid and and "IsActive" filter which is a
> Boolean.  So, the method signature would look something like this:
> GetCustomerList(Guid StateID, Boolean IsActive).  The main issue I am
> encountering is how to allow the user to specify that no filtering will be
> used.
>
> One option is as follows:  GetCustomerList(Boolean FilterByStateID, Guid
> StateID, Boolean FilterByIsActive, Boolean IsActive).  The related stored
> procedure could then use all 4 pieces of information to return the proper
> records.
>
> Another option would be to allow users to provide a"special" value to a
> filter to indicate that it should not be used.  Although this might work
for
> a Guid filter (say,  '{11111111-1111-1111-1111-111111111111}'), there is
no
> way to indicate "no filter" for a boolean filter.
>
> I am leaning toward the first option but would greatly appreciate any
> feedback.  Is there a common technique for handing this type of
requirement.
> Have there been any helpful articles written on the subject?
>
> Thanks!
> Chris
>
>

"JT" <some***@microsoft.com> wrote in message
news:%23iWcuPMiFHA.2156@TK2MSFTNGP14.phx.gbl...
> When I have a parameterized where clause, I typically set parameters not
> specified to NULL. For example:
>
> select
>    *
> from
>    Orders
> where
>    (@Region is NULL or Region = @Region) and
>    (@DiscountCode is NULL or DiscountCode = @DiscountCode)
>
> If you are asking about standard methodologies for defining a class
> interface for a data access component, then perhaps this would be better
> for
> a csharp or oop newsgroup.
>
> "ChrisB" <pleasereplytogr***@thanks.com> wrote in message
> news:Ooz4sxLiFHA.3568@tk2msftngp13.phx.gbl...
>> Hello:
>>
>> I would like to add filters to some Sql Server 2000 queries and was
>> wondering if someone might be able to recommend a proven design pattern.
>>
>> To be more specific, like most applications, I have several queries that
>> reduce the number of returned records through the use of filtering.
>> Ideally, a user should have the option to provide a value for a filter or
>> specify that the filter is not being used.
>>
>> For example, Customer.GetCustomerList() might require 2 filters such as a
>> "StateID" filter which is a Guid and and "IsActive" filter which is a
>> Boolean.  So, the method signature would look something like this:
>> GetCustomerList(Guid StateID, Boolean IsActive).  The main issue I am
>> encountering is how to allow the user to specify that no filtering will
>> be
>> used.
>>
>> One option is as follows:  GetCustomerList(Boolean FilterByStateID, Guid
>> StateID, Boolean FilterByIsActive, Boolean IsActive).  The related stored
>> procedure could then use all 4 pieces of information to return the proper
>> records.
>>
>> Another option would be to allow users to provide a"special" value to a
>> filter to indicate that it should not be used.  Although this might work
> for
>> a Guid filter (say,  '{11111111-1111-1111-1111-111111111111}'), there is
> no
>> way to indicate "no filter" for a boolean filter.
>>
>> I am leaning toward the first option but would greatly appreciate any
>> feedback.  Is there a common technique for handing this type of
> requirement.
>> Have there been any helpful articles written on the subject?
>>
>> Thanks!
>> Chris
>>
>>
>
>