SQL Server 2008 – How to Check if Range Between Two Numbers is Available in Table of Ranges

sql-server-2008

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 ?

Best Answer

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.

Related Question