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)
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!


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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Remove () 10 41
SQL - Aging Report - Display Months with no data 8 43
Begin Transaction 12 26
Query Task 8 19
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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