问题描述
在我的表中有两列FromMinutes和ToMinutes）我输入的数据如5-15,16-30,31-60
我的表结构是：
CREATE TABLE HRM。 tbAttendanceTimeSlots
（
SlotId INT NOT NULL IDENTITY（1,1）PRIMARY KEY，
FromMinutes INT NULL，
ToMinutes INT NULL
）
我尝试过：
i试过
如果出现（选择1来自HRM.tbAttendanceTimeSlots WHERE（@FromMinutes BETWEEN fromMinutes and Tominutes）和（@ToMinutes BETWEEN BETMEEN fromMinutes AND Tominutes））
BEGIN
返回-1 - 删除
结束
ELSE
BEGIN
INSERT INTO HRM.tbAttendanceTimeSlots
（
FromMinutes，ToMinutes
）
VALUES
（
@ FromMinutes，@ ToMinutes
）
返回1
END
There are two columns FromMinutes and ToMinutes) in my table and i entered data like 5-15 , 16-30, 31-60
Now i want to prevent clashing of range when i enter new range. E.g. new range cant be between or overlap existing range like 7-10 is invalid.12-25 is invalid and 1-70 is invalid. How to write this query in insert procedure?
my table structure is :
CREATE TABLE HRM.tbAttendanceTimeSlots
(
SlotId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FromMinutes INT NULL,
ToMinutes INT NULL
)
What I have tried:
i tried
IF EXISTS(SELECT 1 FROM HRM.tbAttendanceTimeSlots WHERE (@FromMinutes BETWEEN FromMinutes AND Tominutes) AND (@ToMinutes BETWEEN FromMinutes AND Tominutes))
BEGIN
RETURN -1 --clashing
END
ELSE
BEGIN
INSERT INTO HRM.tbAttendanceTimeSlots
(
FromMinutes, ToMinutes
)
VALUES
(
@FromMinutes,@ToMinutes
)
RETURN 1
END
推荐答案
IF EXISTS(SELECT 1 FROM HRM.tbAttendanceTimeSlots WHERE (@FromMinutes BETWEEN FromMinutes AND Tominutes) OR (@ToMinutes BETWEEN FromMinutes AND Tominutes))
BEGIN
RETURN -1 --clashing
END
ELSE
BEGIN
INSERT INTO HRM.tbAttendanceTimeSlots
(
FromMinutes, ToMinutes
)
VALUES
(
@FromMinutes,@ToMinutes
)
RETURN 1
END
IF EXISTS
(
SELECT 1
FROM HRM.tbAttendanceTimeSlots
WHERE @FromMinutes <= ToMinutes
AND @ToMinutes >= FromMinutes
)
这篇关于如何防止新数据在SQL表中输入或重叠现有范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！