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