SQL - 如果 OLEQuery 中存在，则选择列

Question

在某些客户中，我使用较旧的数据库，但现在我必须为新客户添加另一列。我的程序现在应查询新列(如果存在(，如果不存在，则使用 NULL。

使用 C# 和 OLEQuery 到目前为止，这是有效的：

string sql = @"SELECT i.[ID] AS cID, 
                        pg.[Name], 
                        pg.[ID] AS pgid, 
                        i.[time],
                        defect = CASE i.[defect] WHEN 0 THEN 'OK' WHEN 1 THEN 'NOK' END,                                           
                  FROM inspection AS i WITH (NOLOCK) INNER JOIN programs as pg ON i.programid = pg.[ID]
                  WHERE i.[time] BETWEEN ... ///SOME MORE CODE IS FOLLOWING HERE!
                    ";

但是现在我想在其中添加(可能/可能不会(现有的列 timeTo。

string sql = @"SELECT i.[ID] AS cID, 
                        pg.[Name], 
                        pg.[ID] AS pgid, 
                        i.[time],
                        defect = CASE i.[defect] WHEN 0 THEN 'OK' WHEN 1 THEN 'NOK' END,                      
                        timeTo = CASE i.[timeTo] WHEN EXISTS THEN i.[timeTo] ELSE NULL END                     
                  FROM inspection AS i WITH (NOLOCK) INNER JOIN programs as pg ON i.programid = pg.[ID]
                  WHERE i.[time] BETWEEN ... //SOME MORE CODE IS FOLLOWING HERE!
                    ";

我知道这个"存在"关键字很困难，但是有没有"单行"解决方案？我找到了几个额外的案例选择解决方案。由于我需要在具有不同列的几个地方使用此解决方案，因此不适合大小写选择解决方案。感谢您的帮助

Answer 1

SQL Server 将不允许您引用不存在的字段。 我怀疑这是因为查询优化器无法生成包含不存在字段的执行计划。

你的应用需要：

1. 确定它正在使用的数据库版本。
2. 选择适当的查询。
3. 确保它处理缺失的数据(通过关闭依赖于新字段的功能或选择适当的默认值(。

Answer 2

您可以执行 if 语句并查询系统表。您需要在动态 sql 中执行此操作才能对其进行解析。

declare @sql nvarchar(max);
if exists (
    select 1 from sys.columns c
    inner join sys.tables t on c.object_id = t.object_id
    where c.name = 'timeTo' and t.name = 'inspection')
begin
    set @sql = 'SELECT i.[ID] AS cID, 
        pg.[Name], 
        pg.[ID] AS pgid, 
        i.[time],
        defect = CASE i.[defect] WHEN 0 THEN ''OK'' WHEN 1 THEN ''NOK'' END,                      
        timeTo = CASE i.[timeTo] WHEN EXISTS THEN i.[timeTo] ELSE NULL END                     
    FROM inspection AS i WITH (NOLOCK) INNER JOIN programs as pg ON i.programid = pg.[ID]
    WHERE i.[time] BETWEEN ... //SOME MORE CODE IS FOLLOWING HERE!';
end
else 
begin
    set @sql = 'SELECT i.[ID] AS cID, 
        pg.[Name], 
        pg.[ID] AS pgid, 
        i.[time],
        defect = CASE i.[defect] WHEN 0 THEN ''OK'' WHEN 1 THEN ''NOK'' END,                                           
    FROM inspection AS i WITH (NOLOCK) INNER JOIN programs as pg ON i.programid = pg.[ID]
    WHERE i.[time] BETWEEN ... ///SOME MORE CODE IS FOLLOWING HERE!';
end
exec (@sql);