Select statement advice needed please

[lsmyth1717]

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 b_publications b_consultation b_freedom etc
Stephen 0 1 0
Steve 1 0 1
Laura 1 0 0
Andrea 0 0 0
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

 

[vongrunt]

Without any structural change, personally I'd go full three-state:

CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
@publications int=null,
@consultation int=null,
...
AS
SET NOCOUNT ON

SELECT u_logon_name, u_firstname, u_surname, u_account_name
FROM UserObject
WHERE
(@publications IS NULL OR b_publications = @publications) OR
(@consultation IS NULL OR b_consultation = @consultation) OR
...
GO

That way sproc call is backward-compatible (you can use 1 and NULL as before) but all arguments are optional (less to type :X) and distinction between NULL and 0 is absolute.

Not less important, stored proc is extensible - you can also search for users that have some flags turned off - if/when necessary of course. To get that functionality front-end user interface must be changed - 2-state checkboxes aren't enough.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[lsmyth1717]

I tried this and now it seems to return all my results even though i don't and it to. Any ideas why this is happening.

CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
@publications int = null,
@consultation int = null,
@freedom int = null,
@judgments int = null,
@legislation int = null,
@policy int = null,
@press int = null,
@questions int = null,
@strategies int = null,
@targets int = null,
@using int = null,
@judgment int = null,
@sentence int = null,
@practice int = null,
@family int = null
AS

SET NOCOUNT ON

SELECT u_logon_name, u_firstname, u_surname, u_account_name
FROM UserObject
WHERE
@publications IS NULL OR b_publications = @publications OR
(@consultation IS NULL OR b_consultation = @consultation) OR
(@freedom IS NULL OR b_freedom = @freedom) OR
(@judgments IS NULL OR b_judgments = @judgments) OR
(@legislation IS NULL OR b_legislation = @legislation) OR
(@policy IS NULL OR b_policy = @policy) OR
(@press IS NULL OR b_press = @press) OR
(@questions IS NULL OR b_questions = @questions) OR
(@strategies IS NULL OR b_strategies = @strategies) OR
(@targets IS NULL OR b_targets = @targets) OR
(@using IS NULL OR b_using = @using) OR
(@judgment IS NULL OR b_judgment = @judgment) OR
(@sentence IS NULL OR b_sentence = @sentence) OR
(@practice IS NULL OR b_practice = @practice) OR
(@family IS NULL OR b_family = @family)
GO

 

[vongrunt]

Put first expression also into ().

And... when user select publications and consultation, do you want to return users that have both (AND) or any (OR) of these two flags set?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba