dcsimg
Results 1 to 5 of 5

Thread: [RESOLVED] Stored procedure, optional parameters

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

    Resolved [RESOLVED] Stored procedure, optional parameters

    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.
    Code:
    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!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,792

    Re: Stored procedure, optional parameters

    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:
    Code:
    WHERE (@CertificationID IS NULL OR xtblEmpCertificationRecord.CertificationID = @CertificationID)
        AND 
    		(
    		   EmpTeamDepartments.EmpDepartmentID = ISNULL(@ST, @Implementation)
    		OR EmpTeamDepartments.EmpDepartmentID = ISNULL(@Implementation, @ST)
    		)

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

    Re: Stored procedure, optional parameters

    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!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

    Re: Stored procedure, optional parameters

    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:
    Code:
    ALTER PROCEDURE [dbo].[xsp_GetAllCertificationsMatchingCriteria] 
    	@CertificationID int = null,
    	
    	@ST int = null,
    	@MST int = null,
    	@Implementation int = null
    
    AS BEGIN
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

    Re: Stored procedure, optional parameters

    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
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width