Complex Select statement advise needed please

Hey there. I was wondering if someone could advise me whether or not i've
writted an effective/efficent stored procedure.
Basically the below procedure will be run from a .net application and the
values passed the the parameters will be 1 or null. It will allow the user
to select as many or as little options with like from a checkbox list and
based on what is entered a 1 or null value will be passed in and a results
set passed back.
I was wondering if this is the best way to do such a query or am i on the
wrong track below works fine but I don't know if its the best way to go abou
t
things.
I'd also like to try and order the results somehow but i'm not sure how to
do this as I've know way of knowing how many of the results are part of each
group. The table i'm querying looks like this.
u_forname u_surname b_publications b_consultation b_freedom b_policyt
etc
Stephen Cairns 0 1
0 0
Steve Jones 1 0
1 0
Laura McCall 1 0
0 0
Andrea Jones 0 0
0 1
etc.................
Basically when users can search the table for results equal to 1 from the
fields which they select in the checkbox list.
I hope someone is able to advise me. Thanks for your help
Here is the stored procedure
CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
@.publications int,
@.consultation int,
@.freedom int,
@.judgments int,
@.legislation int,
@.policy int,
@.press int,
@.questions int,
@.strategies int,
@.targets int,
@.using int,
@.judgment int ,
@.sentence int,
@.practice int,
@.family int
AS
SET NOCOUNT ON
SELECT u_logon_name, u_firstname, u_surname, u_account_name
FROM UserObject
WHERE
[b_publications] = @.publications OR
([b_consultation] = @.consultation) OR
([b_freedom] = @.freedom) OR
([b_judgments] = @.judgments) OR
([b_legislation] = @.legislation) OR
([b_policy] = @.policy) OR
([b_press] = @.press) OR
([b_questions] = @.questions) OR
([b_strategies] = @.strategies) OR
([b_targets] = @.targets) OR
([b_using] = @.using) OR
([b_judgment] = @.judgment) OR
([b_sentence] = @.sentence) OR
([b_practice] = @.practice) OR
([b_family] = @.family)
GOStephen
Read up this article
http://www.sommarskog.se/dyn-search.html
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:E693AF7A-FA77-4E90-8F5B-4F944E0E3C91@.microsoft.com...
> Hey there. I was wondering if someone could advise me whether or not i've
> writted an effective/efficent stored procedure.
> Basically the below procedure will be run from a .net application and the
> values passed the the parameters will be 1 or null. It will allow the
user
> to select as many or as little options with like from a checkbox list and
> based on what is entered a 1 or null value will be passed in and a results
> set passed back.
> I was wondering if this is the best way to do such a query or am i on the
> wrong track below works fine but I don't know if its the best way to go
about
> things.
> I'd also like to try and order the results somehow but i'm not sure how to
> do this as I've know way of knowing how many of the results are part of
each
> group. The table i'm querying looks like this.
> u_forname u_surname b_publications b_consultation b_freedom b_policyt
> etc
> Stephen Cairns 0 1
> 0 0
> Steve Jones 1 0
> 1 0
> Laura McCall 1 0
> 0 0
> Andrea Jones 0 0
> 0 1
> etc.................
> Basically when users can search the table for results equal to 1 from the
> fields which they select in the checkbox list.
> I hope someone is able to advise me. Thanks for your help
> Here is the stored procedure
> CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
> @.publications int,
> @.consultation int,
> @.freedom int,
> @.judgments int,
> @.legislation int,
> @.policy int,
> @.press int,
> @.questions int,
> @.strategies int,
> @.targets int,
> @.using int,
> @.judgment int ,
> @.sentence int,
> @.practice int,
> @.family int
> AS
> SET NOCOUNT ON
> SELECT u_logon_name, u_firstname, u_surname, u_account_name
> FROM UserObject
> WHERE
> [b_publications] = @.publications OR
> ([b_consultation] = @.consultation) OR
> ([b_freedom] = @.freedom) OR
> ([b_judgments] = @.judgments) OR
> ([b_legislation] = @.legislation) OR
> ([b_policy] = @.policy) OR
> ([b_press] = @.press) OR
> ([b_questions] = @.questions) OR
> ([b_strategies] = @.strategies) OR
> ([b_targets] = @.targets) OR
> ([b_using] = @.using) OR
> ([b_judgment] = @.judgment) OR
> ([b_sentence] = @.sentence) OR
> ([b_practice] = @.practice) OR
> ([b_family] = @.family)
> GO