如何从所有DateTime字段中获取所有值，而无需对字段名称进行硬编码？

I have a table, tabEvent, with 11 fields that are DateTime. I want to query every DateTime field in tabEvent, and if any values are > '4/1/2014' and < dateDate(), then return that row.

我有一个表tabEvent,有11个字段是DateTime。我想查询tabEvent中的每个DateTime字段,如果有任何值>'4/1/2014'和 (),则返回该行。

But I don't want to hard-code the field names, because they are always changing (the site is growing and morphing constantly).

但我不想对字段名称进行硬编码,因为它们总是在变化(网站不断增长和不断变形)。

I have the snippet below which returns me the field names, but is not a query on the table (yet).

我有下面的代码片段返回字段名称,但不是表格上的查询(还)。

select c.name ColumnName
from sys.columns c
join sys.types t on (c.user_type_id = t.user_type_id)
where object_name(c.OBJECT_ID) = 'tabEvent'
  and t.name = 'datetime'
order by c.OBJECT_ID

English translation: I'm trying to code a proc that will, each time I log into the admin page, check the tabEvent table for any key dates that have passed (since the last notification) and send a message to me, the admin. Then it will update the notification-date value to today, for next time's running of the proc.

英语翻译:我正在尝试编写一个proc,每当我登录管理页面时,检查tabEvent表中是否已经过去的任何关键日期(自上次通知以来)并向我发送消息,即admin。然后它会将通知日期值更新为今天,以便下次运行proc。

Any help is appreciated!

任何帮助表示赞赏!

1 个解决方案

#1

You can achieve this using dynamic sql:

你可以使用动态sql实现这一点:

DECLARE @Startdate VARCHAR(10)= '2014-04-01' 
DECLARE @EndDate VARCHAR(10) = convert(VARCHAR(10),GETDATE(),20)

DECLARE @sql nVARCHAR(max)

DECLARE @where nVARCHAR(max)
SELECT @where = ' WHERE ' +  STUFF((select c.name  + ' BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''' AND '
from sys.columns c
join sys.types t on (c.user_type_id = t.user_type_id)
where object_name(c.OBJECT_ID) = 'tabEvent'
  and t.name = 'datetime'
order by c.OBJECT_ID
FOR XML PATH('')),1,0,'')

SELECT @sql =   'SELECT * FROM tabEvent' + LEFT(@where,LEN(@where)-4)

EXECUTE sp_executesql @sql