• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Compare a set of bit fields

I have two tables.  One contains workorders and the requirements for that workorder.  Another contains drivers and their qualifications.  Each contains bit fields:


Lets assume that I have a workorder record that has [hasRefrigerated], [hasSwitcher], and [hasSchoolbus] all set to true and the other fields set to false.  I want to create a stored procedure that passes the workorder requirements as parameters and query all of the records in the driver table and return all drivers that have the same fields set to true.

What I cannot seem to wrap my head around is this: if I test all of these six fields against the values in the drivers table, I'll never get the right results.  Example

Parameters passed from values from current workorder:
@hasRefrigerated = true
@hasFlatbed = false
@hasSwitcher = true
@hasDumpTruck = false
@hasForklift = false
@hasSchoolbus = true

A Driver record:
hasRefrigerated = true
hasFlatbed = true
hasSwitcher = true
hasDumpTruck = true
hasForklift = true
hasSchoolbus = true

If my stored procedure has a WHERE clause like this:

          [driver].hasRefrigerated = @hasRefrigerated AND
          [driver].hasFlatbed = @hasFlatbed AND
          [driver].hasSwitcher = @hasSwitcher AND
          [driver].hasDumpTruck = @hasDumpTruck AND
          [driver].hasForklift = @hasForklift AND
          [driver].hasSchoolbus = @hasSchoolbus

Then the driver will not be returned as qualified even though he definately is.  I need a way to only test against the parameters that have a value set to true.  So that it would perform like this:

          [driver].hasRefrigerated = @hasRefrigerated AND        
          [driver].hasSwitcher = @hasSwitcher AND        
          [driver].hasSchoolbus = @hasSchoolbus

and completely ignore these because the parameters are set to false:

          [driver].hasFlatbed = @hasFlatbed AND
          [driver].hasDumpTruck = @hasDumpTruck AND
          [driver].hasForklift = @hasForklift AND

I'm sure I could build a string and pass it through an EXEC, but that seems clumsy and I'm sure there is way to do what I want, I just don't know how.
  • 2
1 Solution
          ([driver].hasRefrigerated = @hasRefrigerated OR @hasRefrigerated = FALSE) AND
          ([driver].hasFlatbed = @hasFlatbed OR  @hasFlatbed = FALSE) AND
          ([driver].hasSwitcher = @hasSwitcher OR  @hasSwitcher = FALSE) AND
          ([driver].hasDumpTruck = @hasDumpTruck OR  @hasDumpTruck = FALSE) AND
          ([driver].hasForklift = @hasForklift OR  @hasForklift = FALSE) AND
          ([driver].hasSchoolbus = @hasSchoolbus OR  @hasSchoolbus = FALSE)
Aneesh RetnakaranDatabase AdministratorCommented:

          [driver].hasRefrigerated = CASE(@hasRefrigerated) WHEN '0' THEN hasRefrigerated ELSE @hasRefrigerated  END AND
          [driver].hasFlatbed = CASE(@hasFlatbed) WHEN '0' THEN hasFlatbed ELSE @hasFlatbed  END  AND
          [driver].hasSwitcher = CASE(@hasSwitcher) WHEN '0' THEN hasSwitcher ELSE @hasSwitcher  END   AND
          [driver].hasDumpTruck = CASE(@hasDumpTruck) WHEN '0' THEN hasDumpTruck ELSE @hasDumpTruck  END   AND
          [driver].hasForklift = CASE(@hasForklift) WHEN '0' THEN hasForklift ELSE @hasForklift  END   AND
          [driver].hasSchoolbus = CASE(@hasSchoolbus) WHEN '0' THEN hasSchoolbus ELSE @hasSchoolbus  END  
manicsquirrelAuthor Commented:
Ahhh, you beat me to it.  I did some more digging and decided to use this and it worked.  Morisce - your's is the closest.  I do have a question though.  I'm testing the parameter for false first.  Does the order matter since I'm using OR?

ALTER PROCEDURE [dbo].[dmsGetDriversByExperience]
      @hasRefrigerated bit,
      @hasFlatbed bit,
      @hasDumpTruck bit,
      @hasSwitcher bit,
      @hasForkTruck bit

      SELECT      *
      FROM      dbo.dmsDriver
            (@hasRefrigerated = 0 OR Qualifications_Refrigerated = @hasRefrigerated) AND
            (@hasFlatbed = 0 OR Qualifications_Flatbed = @hasFlatbed) AND
            (@hasDumpTruck = 0 OR Qualifications_Dumptruck = @hasDumpTruck) AND
            (@hasSwitcher = 0 OR Qualifications_Switcher = @hasSwitcher) AND
            (@hasForkTruck = 0 OR Qualifications_Forktruck = @hasForkTruck)
Good feeling !
I'm not sure if in this case the order is relevant.
It's a good practice whatever the programming language.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now