Help with Boolean check box combinations

• Hogie503

  Mr or Mrs. 500

  Points: 543

  More actions

  June 17, 2021 at 3:20 am

  #3897265

  Hi SQL Experts,

  I have a Store Procedure with snippets of code listed below. with the exception of the Id all other parameters represent a check box, when I select individual check box I get the correct results, but when I select multiple check boxes I get no result.

  example:  If I check the first check box (@Incomplete_Requisition = 1 AND Pom.Status = 0) I get 5 records where all Pom.Status = 0

  If I check the second box (@Submitted_Requisition = 1 AND Pom.Status = 2) I get 10 records where all Pom.Status = 2

  When I check both check boxes I get no results when I should be getting 15 records, can some one please show me how to code the where statement to get the correct results..

  Thanks you.

  @user-id AS int,

  @Incomplete_Requisition AS Bit = 0,

  @Submitted_Requisition AS Bit = 0,

  @Fully_Approved_Requisition AS Bit = 0,

  @Future_Fully_Approve_Requisition AS Bit = 0,

  @Current_Cycle_PO AS Bit = 0,

  @Future_Cycle_PO AS Bit = 0,

  @Cancelled_Closed_PO AS Bit = 0

  Select * From Master as pom

  WHERE

  Pom.UserID = @ID

  AND (@Incomplete_Requisition = 0 OR (@Incomplete_Requisition = 1 AND Pom.Status = 0))

  AND (@Submitted_Requisition = 0 OR (@Submitted_Requisition = 1 AND Pom.Status = 2))

  AND (@Fully_Approved_Requisition = 0 OR (@Fully_Approved_Requisition = 1 AND Pom.Status = 4))

  AND (@Future_Fully_Approve_Requisition = 0 OR (@Future_Fully_Approve_Requisition = 1 AND Pom.Status = 94))

  AND (@Future_Cycle_PO = 0 OR (@Future_Cycle_PO = 1 AND Pom.Status IN (95,98)))

  AND (@Current_Cycle_PO = 0 OR (@Current_Cycle_PO = 1 AND Pom.Status IN (5,6,7,8)))

  AND (@Cancelled_Closed_PO = 0 OR (@Cancelled_Closed_PO = 1 AND pom.Status IN (100,110)))

• pietlinden

  SSC Guru

  Points: 63589

  More actions

  June 17, 2021 at 3:26 am

  #3897266

  Aren't checkbox values either 1 0r 0?

• Steve Collins

  SSCrazy

  Points: 2227

  More actions

  June 17, 2021 at 11:37 am

  #3897417

  If I'm understanding this could this be done in the WHERE clause with a bunch of OR conditions.

  select * 
  From Master as pom
  WHERE Pom.UserID = @userid
        AND ((@Incomplete_Requisition = 1 AND Pom.Status = 0)
          OR (@Submitted_Requisition = 1 AND Pom.Status = 2)
          OR (@Fully_Approved_Requisition = 1 AND Pom.Status = 4)
          OR (@Future_Fully_Approve_Requisition = 1 AND Pom.Status = 94)
          OR (@Future_Cycle_PO = 1 AND Pom.Status IN (95,98))
          OR (@Current_Cycle_PO = 1 AND Pom.Status IN (5,6,7,8))
          OR (@Cancelled_Closed_PO = 1 AND pom.Status IN (100,110)));

  Alternately, you could declare a temporary table, insert statuses based on checkbox values, and then join to the physical table.

  declare @pom        table(status_val        int unique not null);
  
  if @Incomplete_Requisition = 1 
      insert @pom(status_val) values (0);
  if @Submitted_Requisition = 1 
      insert @pom(status_val) values (2);
  if @Fully_Approved_Requisition = 1 
      insert @pom(status_val) values (4);
  if @Future_Fully_Approve_Requisition = 1 
      insert @pom(status_val) values (94);
  if @Current_Cycle_PO = 1 
      insert @pom(status_val) values (95),(98);
  if @Future_Cycle_PO = 1 
      insert @pom(status_val) values (5),(6),(7),(8);
  if @Cancelled_Closed_PO = 1 
      insert @pom(status_val) values (100),(110);
  
  Select * 
  From Master pom
       join @pom p on pom.Status=p.status_val
  WHERE Pom.UserID = @userid;

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Hogie503

  Mr or Mrs. 500

  Points: 543

  More actions

  June 17, 2021 at 2:27 pm

  #3897479

  Thank you really appreciate the response. it work great, and I see where I was over thinking the WHERE Clause..