• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • 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:

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.
0
manicsquirrel
Asked:
manicsquirrel
  • 2
1 Solution
 
morisceCommented:
WHERE
          ([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)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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  
0
 
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
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
0
 
morisceCommented:
Good feeling !
I'm not sure if in this case the order is relevant.
It's a good practice whatever the programming language.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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