February 6, 2007 at 2:23 pm
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.
I am having trouble coming up with the query. Can anyone help?
Thanks, Ninel
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')
February 6, 2007 at 2:36 pm
One more thing I forgot to add.... 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 =1
sStatusCleared =0
Based on these I need to produce the report to display only the pending and cleared records.
February 6, 2007 at 2:40 pm
There are many ways of accomplishing it, but the easiest is probably to have a parameter for Pending, InReview, and Cleared, passing 0 for not checked or 1 for checked. If the user checks All, simply pass a 1 for all of them. Other options are using bits for each checkbox, a delimited string of options that were checked, which you could parse, or using dynamic sql, integrate into an IN(), etc., but the first one should serve you well enough. If you go that route, then the WHERE clause would look something like (untested):
WHERE
iStatus = CASE WHEN @sStatusAll = 1 THEN iStatus ELSE '' END OR
iStatus = CASE WHEN @sStatusPending = 1 THEN 'Pending' ELSE '' END OR
iStatus = CASE WHEN @sStatusInReview = 1 THEN 'InReview' ELSE '' END OR
iStatus = CASE WHEN @sStatusCleared = 1 THEN 'Cleared' ELSE '' END
Edited to account for the additional info you posted prior to me submitting my response.
February 6, 2007 at 4:05 pm
You might find this article both interesting and helpful.
http://www.sommarskog.se/dyn-search.html#Umachandar
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 7, 2007 at 9:09 am
Hi,
I've noticed that in your table DDL you've created the iStatus column as INT but the INSERT statements have VARCHAR values for the iStatus column. If iStatus is VARCHAR the following should work:
SELECT *
FROM reCUSTOMER
WHERE iStatus IN(CASE @sStatusPending WHEN 1 THEN 'Pending' END,
CASE @sStatusInReview WHEN 1 THEN 'InReview' END,
CASE @sStatusCleared WHEN 1 THEN 'Cleared' END)
OR @sStatusAll = 1
Ade
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply