How do I use a CASE statement in a JOIN in SQL Server?

Question

I have a stored procedure that I want to select a column if the applicationTypeId is in a list of IDs. I am trying to use the CASE statement but I don't know what I am doing wrong. I created a table and added the ApplicationTypeIds to it. The table name is @RelationshipsTypeApplicationId

In other words, what I am trying to do is the following:

1. If the applicationTypeId is in the @RelationshipsTypeApplicationId table
2. Join and select the Name column from the IGCRelationshipTypes table

Here is my original stored procedure

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)

And here is what I updated:

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)

Here is a screenshot of the syntax error:

Can someone shed some light on what I am missing here?

Answer 1

CASE in T-SQL is an expression that returns a single scalar value, it is not for control of flow. See Dirty secrets of the CASE expression. You'll need something like this, but I don't have the energy to try to determine why you have a table named @RelationshipsTypeApplicationId with a single column (because you really shouldn't say IN (SELECT *)) or if you want to do something different when the match does not exist.

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