如果没有开始日期和结束日期的记录，则存储过程

Question

我有一个存储过程如下：

CREATE Procedure [dbo].[GetSleepData]
    @period varchar(30),
    @fitbit varchar(6),
    @customdate datetime null,
    @startdate datetime null,
    @enddate datetime null
    as

    if(@period = 'week')
    Begin
    Set @customdate = Getdate()
        Select Sleepkey, fitbitid,totalMinutesAsleep,totalSleepRecords,totalTimeInBed, datetime from sleep where fitbitid = @fitbit and
        datetime  <=@customdate  datetime= @customdate - 7
    End
    else if(@period = 'month')
    Begin
    Set @customdate = Getdate()
        Select Sleepkey, fitbitid,totalMinutesAsleep,totalSleepRecords,totalTimeInBed, datetime from sleep where fitbitid = @fitbit and 
         datetime<= @customdate and datetime >= @customdate - 30
    End

这很好用。我需要在select语句中添加一个if子句。应该是这样的：

如果没有startDate和/或endDate的记录，它应该添加一个零值的记录。

Answer 1

首先，您可以将语句组合为一个SELECT。

其次，考虑将DATEADD(d, -number, @customdate)用于从日期起的子动作天数，而不是@customdate - 7:

CREATE Procedure [dbo].[GetSleepData]
    @period      varchar(30),
    @fitbit      varchar(6),
    @customdate  datetime   = null,
    @startdate   datetime   = null,
    @enddate     datetime   = null
AS
BEGIN
SET NOCOUNT ON;
-- you should here validate @period like
-- IF @period NOT IN ('week', 'month')
--     RAISEERROR(....) /THROW ...;
SET @customdate = GETDATE();
SELECT Sleepkey, fitbitid,totalMinutesAsleep,
       totalSleepRecords,totalTimeInBed, [datetime]
INTO #temp 
FROM sleep 
WHERE fitbitid = @fitbit 
  AND [datetime] <= @customdate
  AND [datetime] >= @customdate - ( CASE @period 
                                   WHEN 'week' THEN 7
                                   WHEN 'month' THEN 30
                                   ELSE NULL --??? what if period is different
                                 END;
IF NOT EXISTS (SELECT 1 FROM #temp)
BEGIN
  SELECT 0 AS Sleepkey, 0 AS fitbitid, 0 AS totalMinutesAsleep,
         0 AS totalSleepRecords, 0 AS totalTimeInBed, NULL AS [datetime]
END
ELSE
BEGIN
    SELECT Sleepkey, fitbitid,totalMinutesAsleep,
            totalSleepRecords,totalTimeInBed, [datetime]
    FROM #temp;
END
END

不要像数据类型那样命名列：datetime应该被引用为[datetime]。