June 17, 2021 at 3:20 am
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)))
June 17, 2021 at 3:26 am
Aren't checkbox values either 1 0r 0?
June 17, 2021 at 11:37 am
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
June 17, 2021 at 2:27 pm
Thank you really appreciate the response. it work great, and I see where I was over thinking the WHERE Clause..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply