Compare a set of bit fields

Posted on 2006-05-23
Last Modified: 2011-09-20
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.
Question by:manicsquirrel
    LVL 5

    Accepted 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)
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran

              [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  
    LVL 6

    Author Comment

    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
          SET NOCOUNT ON;

          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)
    LVL 5

    Expert Comment

    Good feeling !
    I'm not sure if in this case the order is relevant.
    It's a good practice whatever the programming language.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now