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