And/Or Logic??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

And/Or Logic???

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2007-02-06 : 16:39:34

I create a report based on values passed from a front end. The user selects the statuses they want to see from checkboxes. The user can select a few different statuses.They can either select All, Pending, InReview, Cleared. If they select all I display all records. If they select cleared and pending I need to display only cleared and pending records.

These are the variables being passed:
@sStatusAll (0 If not selected, 1 if selected)
@sStatusPending (0 If not selected, 1 if selected)
@sStatusInReview (0 If not selected, 1 if selected)
@sStatusCleared (0 If not selected, 1 if selected)

In this case I would have to look at:
sStatusAll =0
sStatusPending =1
sStatusInReview =0
sStatusCleared =1

Based on these I need to produce the report to display only the pending and cleared records.

I am having trouble coming up with the query. Can anyone help?

Thanks,
Ninel

[Code]
CREATE TABLE reCUSTOMER (
iID int IDENTITY (1, 1) NOT NULL ,
sCustName varchar(50) NULL,
sSIM varchar(20) NULL,
sPlanCode varchar(20) NULL,
iStatus int NULL)

INSERT reCUSTOMER (sCustName, sSIM, sPlanCode, iStatus )
VALUES('John Smith', '123', 'ABC', 'Pending')
INSERT reCUSTOMER (sCustName, sSIM, sPlanCode, iStatus )
VALUES('Jane Smith', '246', 'DEF', 'InReview')
INSERT reCUSTOMER (sCustName, sSIM, sPlanCode, iStatus )
VALUES('Joan Smith', '789', 'GHI', 'Cleared')
[code]

sshelper
Posting Yak Master

216 Posts

Posted - 2007-02-06 : 17:02:29

Try this query:

SELECT * FROM reCustomer
WHERE (iStatus = 'Pending' AND @sStatusPending = 1) OR (iStatus = 'InReview' AND @sStatusInReview = 1) OR (iStatus = 'Cleared' AND @sStatusCleared = 1) OR @sStatusAll = 1

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources