Server为值范围添加检查约束

Question

好的，我有一个创建插入存储过程的问题。THis过程必须将输入的数据放在一个表上，然后插入另一个表中。我设计了3个表，时间、可用性和联系人Availability.This存储过程将只关注时间和联系人可用性表。

时刻表

TimeID Time1 Time2

• ​
  1. 8:30上午8:59<

​

• ​
  1. 上午9时29分

​

• ​
  1. 上午9:30 9:59

​

时间间隔半小时，直到下午530点，这意味着18次Id。

联系人可用性表

ID日期时间可用性

• 1094 2014-12-12 1 1
• 1094 2014-12-12 2 1
• 1094 2014-12-12 3 2
• 1094 2014-12-12 4 2等

时间列的值与时间表中的时间wit相吻合。

这是使用输入值搜索联系人可用性的存储过程：

  @date date,
  @time varchar(50),
  @lastname varchar(50)
  As
  SELECT    d.Date ,
            d.ContactID ,
            c.FirstName ,
            c.LastName ,
            a.Availability
  FROM      Daniel_Availability a
            JOIN Daniel_AvailabilityStatus d ON a.AVID = d.Availability
            JOIN Daniel_Time t ON d.Time = t.TID
            JOIN Contact c ON d.ContactID = c.ContactID
  WHERE     ( ( t.Time1 < @time )
              AND ( t.time2 > @time )
            )
            AND @lastname = c.lastname
            AND @date = d.Date

我试图完成的是创建一个存储过程，允许用户在时刻表中插入一个新的半小时时间范围，但是新的时间范围不能重复或与已经存在的时间冲突。例如，用户应该能够添加范围为7am-730 7am。用户不应该能够添加一个830上午-9上午范围，因为它已经存在，并且用户不应该能够添加830上午-845上午，因为已经有一个830上午-859上午范围。我希望我的要求是有意义的。

Answer 1

您最大的问题是时间列存储为varchar。比较两个变量(作为整数，或在这种情况下的“时间”)会给出错误的结果。varchar排序顺序为0、1、10、11、12、13、14、15、16、17、18、19、2、20、21、22、23、3、4、5、6、7、8、9，因此，上午10:00将小于上午9:00。如果你在桌子上的时间前缀是0，所以08:00，08:30.

一个更好的方法是建立一个适当的日期时间，并传递一个合适的日期时间来进行比较，所以您可以尝试这样做.

  @StartDate DATETIME,  --<-- This full datetime you want to check like '2014-01-01 08:30'
  @lastname varchar(50)
  As

  DECLARE @CheckDate VARCHAR(10) = CONVERT(VARCHAR(10), @Date, 121),
          @EndDate   DATETIME = DATEADD(MINUTE, 30, @Date)

  SELECT    d.Date ,
            d.ContactID ,
            c.FirstName ,
            c.LastName ,
            a.Availability
  FROM      Daniel_Availability a
            JOIN Daniel_AvailabilityStatus d ON a.AVID = d.Availability
            JOIN Daniel_Time t ON d.Time = t.TID
            JOIN Contact c ON d.ContactID = c.ContactID
  WHERE c.lastname = @lastname
  AND d.Date = CONVERT(DATE, @StartDate)
  AND (
        @StartDate BETWEEN 
            CONVERT(DATETIME, @CheckDate + ' ' + t.Time1) 
        AND CONVERT(DATETIME, @CheckDate + ' ' + t.Time2)
  OR    @EndDate BETWEEN
            CONVERT(DATETIME, @CheckDate + ' ' + t.Time1) 
        AND CONVERT(DATETIME, @CheckDate + ' ' + t.Time2)
  )

要做到这一点，您需要确保日期“2014-01-01”以及时间表中的“8:30”可以转换为有效的日期时间。即选择“转换”(日期时间，“2014-01-01”+“+‘8:30”)