参数化查询以返回特定值或所有值和所有 NULL

我正在使用一个复杂的查询：

SELECT DTH.PointPerson AS Person
    ,(
        CASE 
            WHEN USR.[Deleted] IS NULL
                THEN 'Active'
            ELSE 'Inactive'
            END
        ) AS [Status]
    ,OLT.[Name] AS OrganizationalTeam
    ,PDT.[Name] AS Project
    ,(
        CASE 
            WHEN TSK.NAME IS NULL
                THEN NULL
            WHEN SPT.[Name] + ' - ' + SPT.[Description] IS NULL
                THEN 'KanBan'
            ELSE SPT.[Name] + ' - ' + SPT.[Description]
            END
        ) AS Sprint
    ,COALESCE(STY.[Number], NSS.IncidentNumber) AS Story
    ,TSK.[Name] AS Task
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 2
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS MondayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 3
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS TuesdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 4
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS WednesdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 5
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS ThursdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 6
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS FridayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 7
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS SaturdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 1
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS SundayHours
    ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
FROM DailyTaskHours DTH
LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
LEFT JOIN ProductTeamUser PTU ON TSK.ProductTeamUserId = PTU.PK_ProductTeamUser
LEFT JOIN [User] USR ON PTU.UserId = USR.PK_User
LEFT JOIN OrganizationalTeam OLT ON USR.OrganizationalTeamId = OLT.PK_OrganizationalTeam
WHERE DTH.PointPerson LIKE @userParam
    AND ActivityDate >= @startDateParam
    AND ActivityDate <= @endDateParam
    AND OLT.PK_OrganizationalTeam LIKE @orgTeamPK
GROUP BY DTH.PointPerson
    ,PDT.[Name]
    ,SPT.[Name]
    ,SPT.[Description]
    ,STY.[Number]
    ,NSS.IncidentNumber
    ,TSK.[Name]
    ,OLT.[Name]
    ,USR.[Deleted]
HAVING SUM(DTH.[Hours]) > 0

上面显示的参数(@orgTeamPK、@startDateParam等(是根据用户下拉菜单选择分配的。

它通过 C# 进行操作：

SqlDataSource2.SelectParameters["orgTeamPK"].DefaultValue = teamDropDownList.SelectedItem.Value.ToString();

目前，和OLT。PK_OrganizationalTeam喜欢@orgTeamPK没有按预期工作。

有一个下拉菜单如下：全选、团队 A、团队 B、...

当用户选择"全选"时，我希望它显示所有团队或 NULL 且未分配给团队的团队。目前，我的尝试是传递"%"，但它不返回 NULL。

我可以做这样的事情：

和奥尔特。PK_OrganizationalTeam喜欢@orgTeamPK或OLT。PK_OrganizationalTeam为空

但是，这不起作用，因为如果用户选择团队 A，它将并且@orgTeamPK从 % 重新分配给"团队 A"，它将返回团队 A 和所有空值。

如何让它在选中时仅返回团队 A，而在选择全选时返回所有 NULL 和所有团队？

AND coalesce(OLT.PK_OrganizationalTeam,'') LIKE @orgTeamPK