How to check if range between two numbers is available in table of ranges

Question

I have following table of number ranges

Create table Ranges {
  Id int identity(1,1) primary key,
  RangeFrom int not null,
  RangeTo int not null
}

Insert into Ranges(RangeFrom, RangeTo) VALUES (1,4)
Insert into Ranges(RangeFrom, RangeTo) VALUES (5,9)
Insert into Ranges(RangeFrom, RangeTo) VALUES (10,14)
-- here is free space (15 - 19)
Insert into Ranges(RangeFrom, RangeTo) VALUES (20,24)

And I want to insert a new record for example

Insert into Ranges(RangeFrom, RangeTo) VALUES (15,19) -- Ok
Insert into Ranges(RangeFrom, RangeTo) VALUES (15,30) -- Fail

The question is that before insertion I need to check if the numbers between range in the insert statement are not already used in any other range in the table.

Any Idea ?

Answer 1

You could use an insert trigger to check the inserted values against the table data, and fail if the range already exists.

But since you are using a Stored Proc, you can check in the proc. Something like this:

CREATE PROCEDURE blah (@rangeFrom int, @rangeTo int)
AS
BEGIN
IF EXISTS(
SELECT 1 FROM Ranges 
WHERE @rangeFrom BETWEEN RangeFrom AND RangeTo 
    OR @rangeTo BETWEEN RangeFrom AND RangeTo
    OR RangeTo BETWEEN @rangeTo AND @rangeFrom
    OR RangeFrom BETWEEN @rangeFrom AND @rangeTo
)
return

INSERT INTO Ranges VALUES(@rangeFrom, @rangeTo)


END 

This will work if you can ensure inserts only occur via the Stored Procedure. If someone inserts via t-sql only the trigger could stop that.

Answer 2

select count(*)
from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo) 
                       AS LeadRangFrom 
       from table 
     ) ss 
where @RangeFrom > RangeTo 
  and ( @RangeTo < LeadRangFrom or LeadRangFrom is null) 

If count(*) is one then you can insert
Is that the question you are asking?

Answer 3

As others have mentioned, a trigger would work fine. You do not need to change the stored procedure, just put an INSTEAD OF INSERT trigger on the table.

The trigger will look to see if the RangeFrom value being inserted is between any of the ranges already in the same and then do the same for the RangeTo value. If it passes that logic, it will insert.

If it fails, it will rollback. Note, this only works when you do individual inserts like you posted. If you want to be able to do inserts of more than 1 row of data, you will need to tweak this.

CREATE TRIGGER tI_Ranges ON Ranges
INSTEAD OF INSERT 
AS
BEGIN 
    IF (SELECT COUNT(*) 
        FROM Ranges t 
        CROSS APPLY inserted 
        WHERE inserted.rangefrom BETWEEN t.rangefrom AND t.rangeto) = 0
          AND (SELECT COUNT(*) 
               FROM Ranges t 
               CROSS APPLY inserted 
               WHERE inserted.rangeto BETWEEN t.rangefrom AND t.rangeto) = 0
    BEGIN 
        INSERT INTO Ranges(rangefrom, rangeto)
            SELECT inserted.rangefrom, inserted.rangeto
            FROM inserted
    END
    ELSE 
    BEGIN 
        ROLLBACK TRAN
    END
END
GO