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
x
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