FOR XML PATH produces only first item-CodePudding

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))   ';%')))

CodePudding user response：

I'd suggest an alternative approach: make sure your variable has a delimiter before and after every value (eg: ';2;3;4;5;'), and use an EXISTS query:

SELECT DISTINCT r.Id, r.BookingStatus, r.CurrencyId
FROM Reservations r
WHERE
(
    @roomTypeListLocal IS NULL
OR
    EXISTS
    (
        SELECT 1
        FROM ReservationNights rn
        WHERE rn.ReservationId = r.Id
        AND rn.RoomUseId IS NOT NULL
        AND @roomTypeListLocal LIKE ';'   CAST(rn.RoomUseId As varchar(10))   ';'
    )
)

CodePudding user response：

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.