Solved

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

Posted on 2014-10-22
7
195 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
7 Comments
 
LVL 69

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 69

Accepted Solution

by:
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)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 69

Expert Comment

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

http:#a40398172
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

790 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