sql - 从引用先前声明的变量的 CASE 语句分配 T-SQL 变量

Question

我正在尝试动态定义查询的时间段，但在将用户输入转换为日期时间格式时遇到了问题，如下所示:

declare @ObjectName nvarchar(256) = '%',
        @TimePeriod int = '1',
        @TimeInterval nvarchar(128) = 'month',
        @PastPeriodDate datetime


SET @PastPeriodDate = 
  CASE @TimeInterval 
    WHEN 'hour'     THEN (select DATEADD(hour,-@TimePeriod,getdate())
    WHEN 'day'      THEN (select DATEADD(day,-@TimePeriod,getdate())
    WHEN 'week'     THEN (select DATEADD(week,-@TimePeriod,getdate())
    WHEN 'month'    THEN (select DATEADD(month,-@TimePeriod,getdate())
    WHEN 'year'     THEN (select DATEADD(year,-@TimePeriod,getdate())
    ELSE ''
  END

print @PastPeriodDate

例如，我需要在执行时间之前整整 1 周从“1”“周”转换为日期时间字符串，并且过去曾这样做过，但从未涉及 case 语句。这是为了使用 where 子句过滤以下查询中的结果:

select * 
from table1 t1
where t1.time > @PastPeriodDate

我在尝试运行此查询时收到以下输出:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'ELSE'.

如有任何关于我哪里出错的建议或更有效的方法，我们将不胜感激。

Answer 1

看起来您在每一行 WHEN 上都缺少括号。您应该能够像这样修复和简化:

    DECLARE @ObjectName NVARCHAR(256) = '%' ,
    @TimePeriod INT = '1' ,
    @TimeInterval NVARCHAR(128) = 'month' ,
    @PastPeriodDate DATETIME


SET @PastPeriodDate = CASE @TimeInterval
                        WHEN 'hour'
                        THEN DATEADD(hour, @TimePeriod * -1, GETDATE())
                        WHEN 'day'
                        THEN DATEADD(day, @TimePeriod * -1, GETDATE())
                        WHEN 'week'
                        THEN DATEADD(week, @TimePeriod * -1, GETDATE())
                        WHEN 'month'
                        THEN DATEADD(month, @TimePeriod * -1, GETDATE())
                        WHEN 'year'
                        THEN DATEADD(year, @TimePeriod * -1, GETDATE())
                        ELSE ''
                      END

PRINT @PastPeriodDate