sql - 当过滤器参数为空时绕过 WHERE 子句

Question

我有一个过程，需要绕过输入为空的 WHERE 子句。

请找出以下条件。

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

当输入为空时我面临一个问题，在这种情况下需要避免 where 条件。

例子:

如果@PersonID 为空，那么我的选择查询需要以下结构:

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 

我想避免动态查询。

Answer 1

将每个参数括起来并测试是否为空，即

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