Bypassing the WHERE clause when the filter parameter comes as null

Question

I have a procedure and need to bypass the WHERE clause where input as null.

Please find the below condition.

CREATE PROCEDURE [dbo].[spPERSON_SELECT]    
(
@PersonID INT = NULL,
@OrganizationID INT = NULL,
@ManagerID INT = NULL 
)
AS

BEGIN
SELECT PERSON.name, ORGANIZATION.name,PERSON.manager 
FROM PERSON 
    INNER JOIN ORGANIZATION 
        ON PERSON.person_id = ORGANIZATION.person_id    
WHERE PERSON.person_id = @PersonID  
    and ORGANIZATION.organization_id = @OrganizationID  
    and PERSON.manager_id = @ManagerID 

END
GO

Iam facing an issue while the input as null, at that case need to avoid where condition.

Example:

If @PersonID is null then my select query needs the following structure:

SELECT PERSON.name, ORGANIZATION.name,PERSON.manager 
FROM PERSON 
    INNER JOIN ORGANIZATION 
        ON PERSON.person_id = ORGANIZATION.person_id    
WHERE ORGANIZATION.organization_id = @OrganizationID
    and PERSON.manager_id = @ManagerID 

I would like to avoid dynamic queries.

Answer 1

Bracket each parameter and test for null i.e.

(PERSON.manager_id=@ManagerID or @ManagerID is NUll)