Stored Procedure problem with Report

[natrluvr]

I use the below stored procedure to run a report.

SELECT dbo.tblEmissionUnit.EmissionUnit_ID, dbo.tblNOCs.DateApproved, dbo.tblNOC_EmissionUnits.NOC_ID, dbo.tblAPlant.FacilityCode,
dbo.tblAPlant.PlantName, dbo.tblALocation.Location_Desc, dbo.tblEmissionUnit.AEI_ID, dbo.tblEmissionUnit.Plant_ID, dbo.tblEmissionUnit.StackName,
dbo.tblEmissionUnit.Controls, dbo.tblEmissionUnit.DateObsolete, dbo.tblEmissionUnit.Passive, dbo.tblEmissionUnit.Fugitive,
dbo.tblAPlant.Location_ID, dbo.tblEmissionUnit.MonitoringAndTestingProcedure, dbo.tblEmissionUnit.SamplingRequirements,
dbo.tblEmissionUnit.SamplingFrequency, dbo.tblEmissionUnit.RegulatoryRequirements, dbo.tblEmissionUnit.ApplicableRequirement,
dbo.tblEmissionUnit.RadionuclidesRequiringMeasurement, dbo.tblEmissionUnit.ScheduleForUpgrade, dbo.tblEmissionUnit.OutOfCompliance,
dbo.tblEmissionUnit.CERCLA, dbo.tblEmissionUnit.Description, dbo.tblEmissionUnit.MajorEmissionUnit, dbo.tblNOCs.AbatedPTE,
dbo.tblNOCs.UnabatedPTE, dbo.tblNOCs.ProcessDesc
FROM dbo.tblNOCs RIGHT OUTER JOIN
dbo.tblNOC_EmissionUnits ON dbo.tblNOCs.NOC_ID = dbo.tblNOC_EmissionUnits.NOC_ID RIGHT OUTER JOIN
dbo.tblAPlant RIGHT OUTER JOIN
dbo.tblEmissionUnit ON dbo.tblAPlant.Plant_ID = dbo.tblEmissionUnit.Plant_ID LEFT OUTER JOIN
dbo.tblALocation ON dbo.tblAPlant.Location_ID = dbo.tblALocation.Location_ID ON
dbo.tblNOC_EmissionUnits.EmissionUnit_ID = dbo.tblEmissionUnit.EmissionUnit_ID
WHERE (dbo.tblEmissionUnit.DateObsolete IS NULL) AND (dbo.tblEmissionUnit.CERCLA = 0) AND (dbo.tblAPlant.FacilityCode = @FacilityCode) AND
(dbo.tblNOCs.DateObsolete IS NULL) AND (dbo.tblEmissionUnit.MajorEmissionUnit = @MajorMinor)

Originally dbo.tblEmissionUnit.MajorEmissionUnit was used because I wanted the report to run two seperate reports based on if the MajorEmissionUnit checkbox was checked or not. Now I want it to run as one report. Problem is if I just take away the @MajorMinor clause it will not give me the dbo.tblEmissionUnit.MajorEmissionUnit at all whether checked or unchecked. In the form that it receives this information a user has three options for checking either the majoremissionunit, the passive or the fugitive checkbox. So currently without the @MajorMinor it will only return values that have the diffuse or passive checked. Any thoughts?

Thanks

Sarah

 

[Crowley16]

completely remove "AND (dbo.tblEmissionUnit.MajorEmissionUnit = @MajorMinor)" from the WHERE part, that should get you what you need...

--------------------
Procrastinate Now!

 

[natrluvr]

That's what I started with because that's the conclusion I figured, however for some reason when I do that it will only return data if the diffuse or passive is checked.

Thanks

Sarah