sql if statement checking for null parameter

问题:

I'm trying to pass in a parameter and if the parameter is null I want to set the county Id to itself. If the county Id is not null, then I want to bring back records from all counties in the county collaboration.

I'm getting an incorrect syntax error. Any ideas on how to do this?

    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))

回答1:

Try:

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))

But do rethink your condition, it's too many of subqueries.

回答2:

As far as I understand, you cannot add an if-statement in the way you are within the query - no syntax for that kind of querying. You could do exactly what you're doing with two separate queries in each block (duplicate the SELECT/INNER JOIN), but it is not the most ideal solution.

回答3:

This should do you:

select  p.Id        ,
        p.LastName  ,
        p.FirstName ,
        c.Id ,
        c.Description
from Participant         p
join Application         a on a.ParticipantId = p.Id
join Dictionary.Counties c on c.Id            = a.CountyId
where       @pCountyId is     null
   OR (     @pCountyID is not null
        and c.Id in ( select cc.CountyId 
                      from CountyCollaboration cc
                      join CountyCollaboration cc1 on cc1.CollaborationId = cc.CollaborationId
                                                  and cc1.CountID = @pCountyId
                    )
      )