CREATE FUNCTION for attribute to at most 3 people?

Solution 1:

The query with the GROUP BY can return more than 1 record if there's more than 1 RoomNo.

If you include a WHERE clause for the RoomNo then it can only be 1 COUNT

CREATE FUNCTION CalculateRoomNo
(
    @RoomNo tinyint
)
RETURNS bit
AS 
BEGIN
    IF (SELECT COUNT(*) FROM Passenger WHERE RoomNo = @RoomNo) <= 3 
       RETURN 0

    RETURN 1
END

Demo on db<>fiddle here

Solution 2:

As mentioned by @LukStorms, your query has no filter on RoomNo, therefore it can return multiple rows. A scalar subquery must return a maximum of one row.

But the most correct way to achieve what you are trying to do, is not to use this function at all. Instead you can add another column, and create a unique constraint across that and the RoomNo

ALTER TABLE Passenger
    ADD RoomNoPax tinyint NOT NULL
        CONSTRAINT CHK_RoomNoPax CHECK (RoomNoPax >= 1 AND RoomNoPax <= 3);

ALTER TABLE Passenger
    ADD CONSTRAINT UQ_RoomNo_RoomNoPax UNIQUE (RoomNo, RoomNoPax);

db<>fiddle

You now have an extra column which must have the value 1, 2 or 3. And there is a unique constraint over every pair of that value and the RoomNo, so you cannot now put more than 3 Passenger in each RoomNo.