asked on

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:

hasRefrigerated
hasFlatbed
hasSwitcher
hasDumpTruck
hasForklift
hasSchoolbus

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:

WHERE
[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:

WHERE
[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.

ASKER CERTIFIED SOLUTION

morisce

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

WHERE

[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

manicsquirrel

ASKER

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

      SELECT      *
      FROM      dbo.dmsDriver
      WHERE
            (@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)
END

morisce

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