如何在SQL Server中的JOIN中使用案例语句？

Question

我有一个存储过程，如果 applicationTypeid 在ID列表中，我想选择一个列。我正在尝试使用案例语句，但我不知道我在做什么错。我创建了一个表，并向它添加了 applicationTypeids 。表名是 @ReLationshipStypeApplicationIDID

换句话说，我要做的是以下内容：

1. 如果 applicationTypeID 位于 @relations> @relationshipspepepepepeapeplicationid 表中
2. 加入并从 igcrelationshiptypes 表中选择 name 列，

这是我的原始存储过程

WITH AllCustomers AS
(
    SELECT 
        app.ApplicationId,
        cust.Name,
        ApplicationTypes.TypeName AS ApplicationType,
        ApplicationSources.ApplicationSourceName AS ApplicationSource,
        CreatedDate = app.CreatedDate,
        LastUpdateDate = app.StatusChangeDate,
        app.StatusId,
        InProgress = STUFF((SELECT ';'+#aw.ApplicationWorkflowName
                            FROM #aw
                            WHERE #aw.ApplicationId = app.ApplicationId
                            ORDER BY StepNumber
                            FOR XML PATH('')), 1, 1, ''),
        ActionRequired = (SELECT top 1 1 FROM #aw 
                          WHERE #aw.ApplicationId = app.ApplicationId 
                            AND #aw.WorkflowStatusId = 6),
        AccountId = STUFF((SELECT ';' + acct.AccountId 
                           FROM #accounts AS acct
                           WHERE acct.ApplicationId = app.ApplicationId
                           ORDER BY acct.ParentAccountId
                           FOR XML PATH('')), 1, 1, '')
     FROM 
         [dbo].[Applications] app
     INNER JOIN 
         [dbo].[Customers] AS cust ON cust.CustomerId = app.CustomerId
     INNER JOIN 
         [dbo].[ApplicationTypes] ON ApplicationTypes.ApplicationTypeId = app.ApplicationTypeId
     INNER JOIN 
         [dbo].[ApplicationSources] ON ApplicationSources.ApplicationSourceId = app.ApplicationSourceId
     WHERE 
         app.StatusId <> '4020-8901-64FFE33F06B1' 
         AND (@applicationTypeId IS NULL OR app.ApplicationTypeId = @applicationTypeId)

，这是我更新的内容：

WITH AllCustomers AS
(
    SELECT 
        app.ApplicationId,
        cust.Name,
        ApplicationTypes.TypeName AS ApplicationType,
        ApplicationSources.ApplicationSourceName AS ApplicationSource,
        CreatedDate = app.CreatedDate,
        LastUpdateDate = app.StatusChangeDate,
        app.StatusId,
        InProgress = STUFF((SELECT ';' + #aw.ApplicationWorkflowName
                            FROM #aw
                            WHERE #aw.ApplicationId = app.ApplicationId
                            ORDER BY StepNumber
                            FOR XML PATH('')), 1, 1, ''),
        ActionRequired = (SELECT top 1 1 FROM #aw 
                          WHERE #aw.ApplicationId = app.ApplicationId 
                            AND #aw.WorkflowStatusId = 6),
        AccountId = STUFF((SELECT ';' + acct.AccountId 
                           FROM #accounts as acct
                           WHERE acct.ApplicationId = app.ApplicationId
                           ORDER BY acct.ParentAccountId
                           FOR XML PATH('')), 1, 1, ''),
        CASE
            WHEN app.ApplicationTypeId IN (SELECT * FROM @RelationshipsTypeApplicationId)
                THEN relationshipType.Name  
        END
    FROM 
        [dbo].[Applications] app
    INNER JOIN 
        [dbo].[Customers] AS cust ON cust.CustomerId = app.CustomerId
    INNER JOIN 
        [dbo].[ApplicationTypes] ON ApplicationTypes.ApplicationTypeId = app.ApplicationTypeId
    INNER JOIN 
        [dbo].[ApplicationSources] ON ApplicationSources.ApplicationSourceId = app.ApplicationSourceId
    INNER JOIN 
        [dbo].[IGCRelationshipTypes] AS relationshipType 
                ON CASE 
                       WHEN app.ApplicationTypeId IN (SELECT * FROM @RelationshipsTypeApplicationId)
                           THEN (relationshipType.Name = @relationshipType)
                   END
    WHERE 
        app.StatusId <> '4020-8901-64FFE33F06B1' 
        AND (@applicationTypeId IS NULL OR app.ApplicationTypeId = @applicationTypeId)

这是语法错误的屏幕截图：

有人可以阐明我在这里缺少的东西吗？

Answer 1

case 在T-SQL中是返回单个标量值的表达式，而不是为了控制流程。参见案例表达式的肮脏秘密/a>。您需要这样的东西，但是我没有能力尝试确定一个名为 @relationshipStypeApplicationIdID 带有一个列的表（因为您真的不应该说 在（select *））中，或者如果您想在不存在匹配时做一些不同的事情。

...
ON relationshipType.Name = CASE 
     WHEN app.ApplicationTypeId IN 
          (SELECT * FROM @RelationshipsTypeApplicationId)
     THEN @relationshipType END
...