sql-server - sql if语句检查空参数

Question

我正在尝试传递一个参数，如果该参数为空，我想将县 ID 设置为其自身。如果县ID不为空，那么我想带回县协作中所有县的记录。

我收到一个不正确的语法错误。关于如何做到这一点有什么想法吗？

    DECLARE @pCountyId as int;

    select  p.Id, p.LastName, p.FirstName, c.Id, c.Description
    FROM Participant as p
    INNER JOIN Application as a on p.Id = a.ParticipantId
    INNER JOIN Dictionary.Counties as c on a.CountyId = c.Id
    WHERE 

    If @pCountyId is null 
        BEGIN
            c.Id = c.Id
        END
    ELSE
            c.Id in (SELECT cc.CountyId 
                    FROM CountyCollaboration as cc
                    WHERE cc.CollaborationId = (SELECT cc1.CollaborationId 
                                       FROM CountyCollaboration as cc1
                                       WHERE cc1.CountyId = @pCountyId))

Answer 1

尝试:

WHERE 

(@pCountyId is null) OR 

c.Id in (SELECT cc.CountyId 
                    FROM CountyCollaboration as cc
                    WHERE cc.CollaborationId = (SELECT cc1.CollaborationId 
                                       FROM CountyCollaboration as cc1
                                       WHERE cc1.CountyId = @pCountyId))

但是请重新考虑一下你的条件，子查询太多了。