?
Solved

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

Posted on 2014-10-22
7
Medium Priority
?
229 Views
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.
DropDownList-Instructors.sql
0
Comment
Question by:Sherry
  • 3
  • 3
6 Comments
 
LVL 70

Expert Comment

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

Author Comment

by:Sherry
ID: 40398170
They are set to use 0
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Sherry
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
0
 

Author Comment

by:Sherry
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.
0
 
LVL 70

Expert Comment

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

http:#a40398172
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

621 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