Writing a query with 3 parameters that it needs to meet.

Posted on 2014-10-22
Last Modified: 2014-12-06
Writing a query with 3 parameters that it needs to meet.  I have a temp table that I pull from, then apply the 3 parameters.  Select all is an option in two of the three params.  I'm attaching the query that I have so far.  I can get it to meet all three, but get nothing if 'all' is selected.  I also need to make the instructor field optional on the SSRS report.
Question by:Sherry
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40398163
What values are the params set to to indicate 'ALL'?  0? -1?

Author Comment

ID: 40398170
They are set to use 0
LVL 69

Accepted Solution

Scott Pletcher earned 500 total points
ID: 40398172
SELECT InstructorID, InstructorName
FROM #Instructors
WHERE (@Facility = 0 OR FCRLAS_ID = @Facility)
      AND (@Location = 0 OR RPMLCD_CD = @Location)
      AND (@Reason = 0 OR RPMPGM_ID = @Reason)
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 40398181
I did this.  I'm going to check with a business person to make sure I have a good selection for the parameters so I would be sure to get a list of names.  So far, I get none.  and the report won't run without selecting an instructor.   I'll let you know.  Thanks Scott

Author Comment

ID: 40399860
I'm going to have to wait until Monday to complete this.  There's an issue with the database.  The programs matching to the instructors, doesn't match up with the list of programs for facility, location and program/reason.
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40476388
I believe my comment as linked below should be the solution


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question