CREATE FUNCTION for attribute to at most 3 people?-CodePudding

I am currently doing a hotel booking application on SQL Server 2018, and am trying to write a constraint for the RoomNo attribute of my SQL Server table. Essentially, I want each RoomNo to only be able to have at most 3 person, but ran into an error when trying to do the CREATE FUNCTION.

This are my current code:

CREATE TABLE Passenger
(
    ID smallint ,
    Name varchar (50) NOT NULL,
    Email varchar (319) NULL,
    DOB smalldatetime NOT NULL,
    Gender char (1) NOT NULL CHECK (Gender IN ('M', 'F')),
    RoomNo tinyint NOT NULL,

    CONSTRAINT PK_Passenger PRIMARY KEY NONCLUSTERED (ID),
    CONSTRAINT CHK_Passenger_Gender CHECK (Gender IN ('M', 'F'))
)

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

    RETURN 1
END 
GO

ALTER TABLE Passenger
    ADD CONSTRAINT CHK_RoomNoPax CHECK (dbo.CalculateRoomNo(RoomNo) = 0)
GO

When I add a passenger into the table, if it is formatted like this:

INSERT INTO Passenger 
VALUES (1, 'Rob', '[email protected]', '2017-10-04', 'M', 12)
INSERT INTO Passenger 
VALUES (2, 'Darren', 'Darren@yahoo,com', '1976-12-21', 'F', 12)
INSERT INTO Passenger 
VALUES (3, 'Peggy', '', '2006-03-15', 'F', 12)
INSERT INTO Passenger 
VALUES (4, 'Carlos', '', '1981-04-06', 'F', 12)

It will stop at

INSERT INTO Passenger VALUES (3, 'Peggy', '', '2006-03-15', 'F', 12)

since RoomNo '12' has reached its maximum capacity.

But, if I added the values like such where the room numbers are different from each other:

INSERT INTO Passenger 
VALUES (1, 'Rob', '[email protected]', '2017-10-04', 'M', 69)
INSERT INTO Passenger 
VALUES (2, 'Darren', 'Darren@yahoo,com', '1976-12-21', 'F', 74)
INSERT INTO Passenger   
VALUES (3, 'Peggy', '', '2006-03-15', 'F', 45)
INSERT INTO Passenger 
VALUES (4, 'Carlos', '', '1981-04-06', 'F', 72)
INSERT INTO Passenger 
VALUES (5, 'John', '[email protected]', '1988-05-06', 'M', 69)

It will return an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is there any way I can properly run this SQL?

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

You need to change the logic in the function.

You should be checking for the number of records for any given roomno, not the number of roomno in the entire table.

the subquery should return only one scalar value because you are performing a logical operation.

i.e.

if exists (select 1 from Passenger group by roomno having count(1) <= 3)
begin
return 1
end
else 
begin
return 0
end

In the above query, we are checking for the number of persons assigned to each room number and if there is an existence of such case then it will return 1. In this case, it will not return more than one record.

Please modify the return value as per your requirement.

Please upvote if you find this answer useful