[RESOLVED] Stored procedure, optional parameters

[MMock]

I write stored procedures all the time that take optional parameters so I don't know why I can't get this one to return the results I want.
Simply put, we have employee records with a field saying what type of employee they are. Let's say some emp's can be in the Implementation department and some are Service Technicians (ST's).
I want to run a query to see all the certifications our implementation team *and* ST's have.
I can do implementation alone and ST's alone but when I try to get them together I get no rows returned.

ALTER PROCEDURE [dbo].[xsp_GetAllCertificationsMatchingCriteria] 
	@CertificationID int = null,
	@ST int = null,
	@Implementation int = null
AS BEGIN

SELECT 
<column list>
from xtblEmpCertificationRecord
join xtblEmpCertifications on xtblEmpCertifications.Control = xtblEmpCertificationRecord.CertificationID
join Emps on Emps.EmpID = xtblEmpCertificationRecord.EmpID
join EmpTeamDepartments on EmpTeamDepartments.EmpDepartmentID = Emps.TeamDepartment
WHERE (@CertificationID IS NULL OR xtblEmpCertificationRecord.CertificationID = @CertificationID)
    AND 
		(@ST IS NULL OR EmpTeamDepartments.EmpDepartmentID = @ST)
	AND 
		(@Implementation IS NULL OR EmpTeamDepartments.EmpDepartmentID = @Implementation)

I think this must be fairly easy and somebody smart will see my problem right away, but if you need more info let me know.

Thank you!

[CVMichael]

Well, 5 cannot equal 5 AND 6 at the same time...

So EmpTeamDepartments.EmpDepartmentID can equal @ST OR @Implementation

I would write the where clause like this:

WHERE (@CertificationID IS NULL OR xtblEmpCertificationRecord.CertificationID = @CertificationID)
    AND 
		(
		   EmpTeamDepartments.EmpDepartmentID = ISNULL(@ST, @Implementation)
		OR EmpTeamDepartments.EmpDepartmentID = ISNULL(@Implementation, @ST)
		)

[MMock]

Yeah, I hear you about 5 and 6, LOL!
I tried AND's, OR's, nothing made a difference. I will try your suggestion, thanks!

[MMock]

Wait, sorry, I have a question now that I am looking more closely. The user doesn't have to pick an employee's team. They might only want to pick a certificationID and not a specific team which by default would be all teams. If I say "if ST is null use implementation but if implementation is null use ST" (which I think is the correct explanation of your code), that won't work because both are null. ALso, this could possibly be expanded to allow other choices of teams (we have approximately 10) so how would even one additional criterion work, as in the following:

ALTER PROCEDURE [dbo].[xsp_GetAllCertificationsMatchingCriteria] 
	@CertificationID int = null,
	
	@ST int = null,
	@MST int = null,
	@Implementation int = null

AS BEGIN

[MMock]

Since what I really wanted to do was pass in a list of the emp departments and do an "IN", I solved it : this way