Skip to content
Advertisement

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

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

Advertisement

Answer

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 
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement