CODEWITHSUNDEEP
c#sql.net
Nixdorf
Nixdorf

Reputation: 61

What should I use instead of SQL "OR" statements

I have a gridview that can be filtered by 6 drop down boxes, so when writing the sql the easiest thing would be to use an 'or' statement if dropdown has selection or null etc.

However I have read on here and other sites that using sql or statements are a bad idea, can anyone offer any other suggestions i could use rather than me writing variations on whether each ddl selection is null? Below is an example of the first query, with every ddl returning a value

@ruleID int = null, 
@engagementStatusID int = null,
@areaOfWorkID int = null,
@registered bit = null,
@staffGroupID int = null,
@assignmentTypeID int = NULL

AS
SET NOCOUNT ON

IF (@ruleID IS NOT NULL and @engagementStatusID IS NOT NULL and @areaOfWorkID IS NOT NULL and 
        @registered IS NOT NULL and @staffGroupID IS NOT NULL and @assignmentTypeID IS NOT NULL)

BEGIN
    SELECT            r.dbRuleId AS RuleID,r.dbEngagementStatusId AS EngagementStatusID, 
                  r.dbIsAllStaffGroups AS AllStaffGroups,r.dbIsAllAssignments AS AllAssignments, 
                  r.dbIsAllRegistered AS AllRegistered,r.dbIsAllUnregistered AS AllUnregistered, 
                  r.dbSoftDelete AS Softdelete, es.dbName AS EngagementName, 
                  sgc.dbName AS StaffGroupName, aow.dbName AS AreaOfWorkName, 
                  at.dbName AS AssignmentName, at.dbIsRegistered AS Registered,sgc.dbStaffGroupCodeId AS StaffGroupCodeID, 
                  at.dbAssignmentTypeId AS AssignmentID, aow.dbAreaOfWorkId AS AreaOfWorkID
FROM              dbo.tbRule r INNER JOIN
                  dbo.EngagementStatus es ON r.dbEngagementStatusId = es.dbEngagementStatusId INNER JOIN
                  dbo.RuleStaffGroup rsg ON r.dbRuleId = rsg.dbRuleId INNER JOIN
                  dbo.StaffGroupCode sgc ON rsg.dbStaffGroupId = sgc.dbStaffGroupCodeId INNER JOIN
                  dbo.RuleAssignmentCode rac ON r.dbRuleId = rac.dbRuleId INNER JOIN
                  dbo.AssignmentCode ac ON 
                  rac.dbAssignmentCodeId = ac.dbAssignmentCodeId INNER JOIN
                  dbo.AssignmentType at ON ac.dbAssignmentId = at.dbAssignmentTypeId INNER JOIN
                  dbo.AreaOfWork aow ON ac.dbAreaOfWorkId = aow.dbAreaOfWorkId
    WHERE   ((r.dbRuleId = @ruleID) and (r.dbEngagementStatusId = @engagementStatusID) and (aow.dbAreaOfWorkId = @areaOfWorkID) and
                (at.dbIsRegistered = @registered) and (sgc.dbStaffGroupCodeId = @staffGroupID) and (at.dbAssignmentTypeId = @assignmentTypeID))

Any advice on this would be great

Update I feel i should clarify something about my code, when i say null, this is the value i have assigned to the "all" selection of a drop down list, so for example imn most cases i do something like this to get the value that needs to be passed to the DB

int? Type = (this.ddlType.SelectedValue.ToString() == "All") ? (int?)null : Convert.ToInt32(this.ddlType.SelectedValue.ToString());

so if the user has selected all the Db recieves 'null' which i can then use on the 'if @blah IS NOT NULL' etc. I realise this is probably not the best way to do this

Upvotes: 0

Views: 172

Answers (3)

Mentatmatt
Mentatmatt

Reputation: 525

I'm not sure I understood your question, but if you're looking at avoiding repetition of OR operators, consider using IN('x','y','z') - listing the possible values. This would be easier to read than [something] = 'x' OR [something] = 'y' OR [something] = 'z'.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1064134

The problem comes when you do things like:

WHERE   (r.dbRuleId = @ruleID or @ruleID is null)
and     (r.dbEngagementStatusId = @engagementStatusID
                 or @engagementStatusID is null)
-- ... lots more

which quickly degrades to really bad query plans. The trick, then, is to have TSQL that matches your exact set of query parameters.

The hard to maintain way to fix this is to write DML for every single possibility, and branch into the correct one - but this is really ugly, and confuses a lot of tools.

The easiest way to do this is to build the TSQL appropriately at the caller - but if your system demands that you use a stored procedure (the benefits for which, these days, are dubious at best - btw), then the simplest choice is dynamic SQL. Obviously, you need to be careful here - you still don't want to concatenate inputs (for both injection and query-plan reasons), but - you can do something like:

declare @sql nvarchar(4000) = N'...start of query...';

if(@ruleID is not null)
    set @sql = @sql + N' and r.dbRuleId = @ruleID';
if(@engagementStatusID is not null)
    set @sql = @sql + N' and r.dbEngagementStatusId = @engagementStatusID';

You then need to execute that with sp_executesql, declaring the parameters:

exec 'sp_executesql', @sql,
      N'@ruleID int, @engagementStatusID int',
      @ruleID, @engagementStatusID

Upvotes: 1

Darren
Darren

Reputation: 70786

It seems you are executing this stored procedure and then validating the user input at the database level. You should not call the database stored procedure if the drop down list values are null, you can handle this at the client side (or server side).

Client side (JavaScript) would be better for the users experience, you can then invoke the stored procedure if the user has selected all appropriate drop down list values.

Upvotes: 1

Related Questions