How to only run left join when the variable is not null?-CodePudding

I'm trying to simplify my stored procedure and I have one that is only using left join based on the user id that is passed in. If the user id is null, don't do left join, but if it is null, left join it with another table. How should I re-write it ? Thank you

CREATE OR ALTER PROCEDURE [dbo].[GetRoom] 
    @RoomId UNIQUEIDENTIFIER NULL,
    @UserId UNIQUEIDENTIFIER
AS
BEGIN   
    IF (@UserId IS NULL)
    BEGIN   
        SELECT r.Id, r.DisplayName            
        FROM Room r
        INNER JOIN Game g ON r.GameId = g.Id
        INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id                         
        WHERE r.Id = @RoomId
    END
    ELSE
        SELECT 
            r.Id, r.DisplayName, 
            ru.Description, -- this is coming from the left join table
            ru.Tags         -- from left join table   
        FROM Room r
        INNER JOIN Game g ON r.GameId = g.Id
        INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id          
        LEFT JOIN RoomUser ru ON ru.RoomId = r.Id
        WHERE r.Id = @RoomId AND ru.UserId = @UserId
END

CodePudding user response：

Currently your stored procedure return different number columns depending on the @UserId.

You may remove the IF condition, and combined it as one single query by moving ru.UserId = @UserId to ON condition. This will make it a true LEFT JOIN to table RoomUser.

This will means it always return 4 columns as result

    SELECT r.Id, 
           r.DisplayName, 
           ru.Description,
           ru.Tags        
    FROM   Room r
           INNER JOIN Game g               ON r.GameId     = g.Id
           INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id          
           LEFT JOIN RoomUser ru           ON ru.RoomId    = r.Id
                                          AND ru.UserId    = @UserId
    WHERE  r.Id = @RoomId

CodePudding user response：

Try something like below,

        SELECT 
            r.Id, r.DisplayName, 
            ru.Description, -- this is coming from the left join table
            ru.Tags         -- from left join table   
        FROM Room r
        INNER JOIN Game g ON r.GameId = g.Id
        INNER JOIN ProfileDuplicated pd ON r.HostedById = pd.Id          
        LEFT JOIN RoomUser ru ON ru.RoomId = r.Id  
                              AND r.Id IS NOT NULL 
                              AND r.Id = @RoomId 
                              AND ru.UserId IS NOT NULL
                              AND ru.UserId = @UserId

CodePudding user response：

You can use ISNULL function to check the NULL value of the parameter. (Assuming ru.userId will not be NULL)

WHERE r.Id = @RoomId AND 
ru.UserId = ISNULL(@UserId, ru.UserId)