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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy