FOR XML PATH produces only first item

I have two tables: Reservations and ReservationNights (every reservation has many nights).

In a stored procedure, I have a variable that looks like this: @roomTypeList = '2;3;4;5;' -> its a list of RoomUseIds.

I need to display only the reservations that have a reservation nights with one of those Ids (In this example, it should display only reservation 14105, because there are existing nights where RoomUseId = 3 and the '2;3;4;5;' variable contains 3.

I’ve tried to use something like this, but it doesn’t produce good results – sometimes it displays only 1 reservations when multiple should be displayed, usually it displays nothing.

SELECT DISTINCT r.Id, r.BookingStatus, r.CurrencyId
        FROM Reservations r
        --JOIN ReservationNights rn ON rn.ReservationId = r.Id
        WHERE
(@roomTypeListLocal IS NULL OR (@roomTypeListLocal LIKE ('%' + CAST((SELECT STUFF((SELECT ';' + CAST(rn.RoomUseId as nvarchar(MAX))
                FROM ReservationNights rn
                WHERE rn.ReservationId = r.Id AND rn.RoomUseId IS NOT NULL
                FOR XML PATH('')), 1, 1, '')) as nvarchar(MAX)) + ';%')))

Answer

You key issue is lack of normalization. If you have a list, store it as a list.

So you need a table variable or Table Valued parameter. At a pinch, you can split your existing string, but it’s better to have the data in the right format in the first place.

DECLARE @roomTypeList TABLE (roomType int PRIMARY KEY);
INSERT @roomTypeList (roomType)
SELECT *
FROM STRING_SPLIT(@roomTypeListLocal, ';');

SELECT
  r.Id,
  r.BookingStatus,
  r.CurrencyId
FROM Reservations r
WHERE
    @roomTypeListLocal IS NULL
    OR EXISTS (SELECT 1
        FROM ReservationNights rn
        JOIN @roomTypeList rl ON rl.roomType = rn.RoomUseId
        WHERE rn.ReservationId = r.Id
    );

---

If you really wanted to stick with a comma-separated list, you could merge STRING_SPLIT into the above query, rather than trying to construct an equivalent aggregated string.